Oracle支持在JSON路径文本中进行过滤，您可以使用它来限制数组项。

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

| 瓦尔|子键2|
| - -----|- -----|
| {“key1”：[ { “subkey1”：“somevalue1”， “subkey2”：“somevalue2”， }， { “subkey1”：“interestedvalue1”， “subkey2”：“interestedvalueToParseAndGet”， }]}| interestedvalueToParseAndGet|
fiddle

其中一个选项是使用CASE表达式：

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

最后我使用了JSON_TABLE，下面的工作：

SELECT subkey2 
     FROM table1,
     JSON_TABLE(
       json_data,
       '$.key1[*]'
       COLUMNS (
         subkey1 VARCHAR2(100) PATH '$.subkey1',
         subkey2 VARCHAR2(100) PATH '$.subkey2'
       )
     )
WHERE subkey1 = 'interestedvalue1';