Oracle SQL conditionally parse a json column

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?

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

fiddle

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

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