By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE agents_timesheet (
ID,
name,
health_check_record CHECK (health_check_record IS JSON),
"DATE",
clock_in,
clock_out
) AS
SELECT 1,
'AAA',
'{"mental":{"stress":"no", "depression":"no"},"physical":{"fever":"no", "flu":"no"}}',
DATE '2021-12-06',
INTERVAL '0 08:25:07' DAY(0) TO SECOND,
CAST(NULL AS INTERVAL DAY(0) TO SECOND)
FROM DUAL
UNION ALL
SELECT 2,
'BBB',
'{"mental":{"stress":"no", "depression":"no"},"physical":{"fever":"no", "flu":"yes"}}',
DATE '2021-12-06',
INTERVAL '0 08:26:12' DAY(0) TO SECOND,
CAST(NULL AS INTERVAL DAY(0) TO SECOND)
FROM DUAL
2 rows affected
SELECT a.id,
a.name,
j.*,
a."DATE",
a.clock_in,
a.clock_out
FROM agents_timesheet a
CROSS JOIN JSON_TABLE(
a.health_check_record,
'$'
COLUMNS (
mental_stress VARCHAR2(3) PATH '$.mental.stress',
mental_depression VARCHAR2(3) PATH '$.mental.depression',
physical_fever VARCHAR2(3) PATH '$.physical.fever',
physical_flu VARCHAR2(3) PATH '$.physical.flu'
)
) j
WHERE physical_flu = 'yes';
| ID | NAME | MENTAL_STRESS | MENTAL_DEPRESSION | PHYSICAL_FEVER | PHYSICAL_FLU | DATE | CLOCK_IN | CLOCK_OUT |
|---|---|---|---|---|---|---|---|---|
| 2 | BBB | no | no | no | yes | 06-DEC-21 | +0 08:26:12.000000 | null |
SELECT a.id,
a.name,
j.*,
a."DATE",
a.clock_in,
a.clock_out
FROM agents_timesheet a
CROSS JOIN JSON_TABLE(
a.health_check_record,
'$?(EXISTS(@.physical.fever == "yes"))'
COLUMNS (
mental_stress VARCHAR2(3) PATH '$.mental.stress',
mental_depression VARCHAR2(3) PATH '$.mental.depression',
physical_fever VARCHAR2(3) PATH '$.physical.fever',
physical_flu VARCHAR2(3) PATH '$.physical.flu'
)
) j
WHERE physical_flu = 'yes';
ORA-40597: JSON path expression syntax error ('$?(EXISTS(@.physical.fever == "yes"))')
JZN-00229: Missing parenthesis in paranthetical expression
at position 10