Can Oracle SQL parse a json column conditionally?

AnandRockzz
June 1, 2023
101 views
3 votes
3 Answers

in a DB table, table1 I have the following JSON in column1:

{
  "key1": [
    {
      "subkey1":"somevalue1",
      "subkey2":"somevalue2",
    },
    {
      "subkey1":"interestedvalue1",
      "subkey2":"interestedvalueToParseAndGet",
    }
  ]
}

If I do the following,

select JSON_VALUE(column1, '$.key1[1].subkey2') from table1

I’m able to get interestedvalueToParseAndGet from the query, which is what I’m expecting.

But I cannot guarantee that this will be the second item in the array always.

How to conditionally fetch the item from the array (who’s subkey1 is interestedvalue1) and get its corresponding value for subkey2?

Answers

Chosen as BEST ANSWER
- AnandRockzz
- June 1, 2023 at 9:51 am
- 0 votes
0
I ended up using JSON_TABLE, and following worked:
```
SELECT subkey2 
     FROM table1,
     JSON_TABLE(
       json_data,
       '$.key1[*]'
       COLUMNS (
         subkey1 VARCHAR2(100) PATH '$.subkey1',
         subkey2 VARCHAR2(100) PATH '$.subkey2'
       )
     )
WHERE subkey1 = 'interestedvalue1';
```

(Edit)

One of the options is to use CASE expression:

WITH            -- Sample Data:
    tbl (ID, KEYS) AS
        (   Select 1, '{
                          "key1": [
                            {
                              "subkey1":"somevalue1",
                              "subkey2":"somevalue2",
                            },
                            {
                              "subkey1":"interestedValue1",
                              "subkey2":"interestedValueFromSecond",
                            }
                          ]
                        }' 
           From Dual Union All
           Select 2, '{
                          "key1": [
                            {
                              "subkey1":"interestedValue1",
                              "subkey2":"interestedValueFromFirst",
                            },
                            {
                              "subkey1":"someOtherValue",
                              "subkey2":"notinterested",
                            }
                          ]
                        }' 
           From Dual
      )
--  M a i n   S Q L :
Select ID, CASE WHEN JSON_VALUE(KEYS, '$.key1[0].subkey1') = 'interestedValue1' 
                THEN JSON_VALUE(KEYS, '$.key1[0].subkey2')
                WHEN JSON_VALUE(KEYS, '$.key1[1].subkey1') = 'interestedValue1' 
                THEN JSON_VALUE(KEYS, '$.key1[1].subkey2')
           END "INTERESTED_VALUE" 
From tbl
--
--  R e s u l t :
--  ID  INTERESTED_VALUE
--  --  --------------------------
--   1  interestedValueFromSecond
--   2  interestedValueFromFirst

Oracle supports filtering in JSON Path literal, you may use it to restrict array items.

with sample (val) as (
  select '{
  "key1": [
    {
      "subkey1":"somevalue1",
      "subkey2":"somevalue2",
    },
    {
      "subkey1":"interestedvalue1",
      "subkey2":"interestedvalueToParseAndGet",
    }
  ]
}'
  from dual
)
select
  sample.*,
  json_value(val, '$.key1[*]?(@.subkey1 == "interestedvalue1").subkey2') as subkey2
from sample

VAL	SUBKEY2
{ "key1": [ { "subkey1":"somevalue1", "subkey2":"somevalue2", }, { "subkey1":"interestedvalue1", "subkey2":"interestedvalueToParseAndGet", } ] }	interestedvalueToParseAndGet

fiddle

Please signup or login to give your own answer.

Click here to cancel reply.