CODEWITHSUNDEEP
sqloracle-database
Dhana D.
Dhana D.

Reputation: 1720

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

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":{"other_symptoms":"headache", "flu":"no"}} |             |          |
---------------------------------------------------------------------------------------------------------
 2  | BBB  | {"mental":{"stress":"no", "depression":"no"},          | 6-Dec-2021  | 08:26:12 |
    |      |  "physical":{"other_symptoms":"no", "flu":"yes"}}      |             |          |
---------------------------------------------------------------------------------------------------------
 3  | CCC  | {"mental":{"stress":"no", "depression":"severe"},      | 6-Dec-2021  | 08:27:12 |
    |      |  "physical":{"other_symptoms":"cancer", "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?

Additional question

Based on the table, how can I retrieve full list of other_symptoms, then it will get me this kind of output:

 ID | name |  other_symptoms
-------------------------------
 1  | AAA  | headache
 2  | BBB  | no
 3  | CCC  | cancer

Upvotes: 0

Views: 124

Answers (3)

michal.jakubeczy
michal.jakubeczy

Reputation: 9469

You can use JSON_EXISTS() function.

SELECT *
  FROM agents_timesheet 
 WHERE JSON_EXISTS(health_check_record, '$.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"%';

Upvotes: 2

user5683823
user5683823

Reputation:

You can use "dot notation" to access data from a JSON column. Like this:

select "DATE", id, name
from   agents_timesheet t
where  t.health_check_record.physical.flu = 'yes'
;


DATE          ID  NAME
-----------  ---  ----
06-DEC-2021    2  BBB

Note that this approach requires that you use an alias for the table name (so you can use it in accessing the JSON data).

For testing I used the data posted by MT0 on dbfiddle. I am not a big fan of double-quoted column names; use something else for "DATE", such as dt or date_.

Upvotes: 1

MT0
MT0

Reputation: 167982

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

Upvotes: 1

Related Questions