How to get a value inside of a JSON that is inside a column in a table in Oracle sql?-CodePudding

Suppose that I have a table named agents_timesheet that having a structure like this:

 ID | name |               health_check_record              | date        | clock_in | clock_out  
--------------------------------------------------------------------------------------------------
 1  | AAA  | {"mental":{"stress":"no", "depression":"no"},  | 6-Dec-2021  | 08:25:07 |
    |      |  "physical":{"fever":"no", "flu":"no"}}        |             |          |
--------------------------------------------------------------------------------------------------
 2  | BBB  | {"mental":{"stress":"no", "depression":"no"},  | 6-Dec-2021  | 08:26:12 |
    |      |  "physical":{"fever":"no", "flu":"yes"}}       |             |          |

Now I need to get all agents having flu at the day. As for getting the flu from a single JSON in Oracle SQL, I can already get it by this SQL statement:

SELECT * FROM JSON_TABLE(
   '{"mental":{"stress":"no", "depression":"no"}, "physical":{"fever":"no", "flu":"yes"}}', '$'
   COLUMNS (fever VARCHAR(2) PATH '$.physical.flu')
);

As for getting the values from the column health_check_record, I can get it by utilizing the SELECT statement.

But How to get the values of flu in the JSON in the health_check_record of that table?

CodePudding user response：

You can use JSON_EXISTS() function.

SELECT *
  FROM agents_timesheet 
 WHERE JSON_EXISTS(jscol, '$.physical.flu == "yes"');

There is also "plain old way" without JSON parsing only treting column like a standard VARCHAR one. This way will not work in 100% of cases, but if you have the data in the same way like you described it might be sufficient.

SELECT * 
  FROM agents_timesheet 
 WHERE health_check_record LIKE '%"flu":"yes"%';

CodePudding user response：

How to get the values of flu in the JSON in the health_check_record of that table?

From Oracle 12, to get the values you can use JSON_TABLE with a correlated CROSS JOIN to the table:

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';

db<>fiddle here