Question 179775 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Can Oracle SQL parse a json column conditionally?

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?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    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';

  2. 0

    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
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search