By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id SERIAL PRIMARY KEY, val JSON);
INSERT INTO test (val) VALUES (
'[
{
"widgets": [
{
"name": "Calendar",
"uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152",
"value": "2023-12-23",
"system_name": "Calendar"
},
{
"name": "Number",
"uuid": "7cbdf159-7368-4db8-83e0-775cdd223131",
"value": 3
},
{
"name": "Number",
"uuid": "c8caa756-8563-4811-ac86-9dc0860832b4",
"value": 0
}
]
}
]'
),(
'[
{
"widgets": [
{
"name": "Calendar",
"uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152",
"value": "2023-01-23",
"system_name": "Calendar"
},
{
"name": "Number",
Records: 3 Duplicates: 0 Warnings: 0
| id | CAST(val AS CHAR) |
|---|---|
| 1 | [{"widgets": [{"name": "Calendar", "uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152", "value": "2023-12-23", "system_name": "Calendar"}, {"name": "Number", "uuid": "7cbdf159-7368-4db8-83e0-775cdd223131", "value": 3}, {"name": "Number", "uuid": "c8caa756-8563-4811-ac86-9dc0860832b4", "value": 0}]}] |
| 2 | [{"widgets": [{"name": "Calendar", "uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152", "value": "2023-01-23", "system_name": "Calendar"}, {"name": "Number", "uuid": "7cbdf159-7368-4db8-83e0-775cdd223131", "value": 3}, {"name": "Number", "uuid": "c8caa756-8563-4811-ac86-9dc0860832b4", "value": 0}]}] |
| 3 | [{"widgets": [{"name": "Date", "uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152", "value": "2023-12-23", "system_name": "Date"}, {"name": "Number", "uuid": "7cbdf159-7368-4db8-83e0-775cdd223131", "value": 3}, {"name": "Number", "uuid": "c8caa756-8563-4811-ac86-9dc0860832b4", "value": 0}]}] |
SELECT test.id, CAST(test.val AS CHAR) val
FROM test
CROSS JOIN JSON_TABLE(test.val,
'$[0].widgets[0]' COLUMNS ( name VARCHAR(100) PATH '$.name',
value VARCHAR(100) PATH '$.value'
)
) jsontable
WHERE name = 'Calendar' AND CAST(value AS DATE) BETWEEN '2023-12-01' AND '2023-12-31';
| id | val |
|---|---|
| 1 | [{"widgets": [{"name": "Calendar", "uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152", "value": "2023-12-23", "system_name": "Calendar"}, {"name": "Number", "uuid": "7cbdf159-7368-4db8-83e0-775cdd223131", "value": 3}, {"name": "Number", "uuid": "c8caa756-8563-4811-ac86-9dc0860832b4", "value": 0}]}] |