Selecting a record based off conditions CLOB I have a table with a column storing JSON data as a CLOB. The JSON has a 'miscData' field that is made up of an array of JSON objects. Each object has 2 keys which are always the same, but the values are different.
.
.
.
&quot;otherKeys&quot; : &quot;otherValues&quot;,
&quot;miscData&quot;: [
        {
            &quot;miscType&quot;: &quot;date&quot;,
            &quot;miscText&quot;: &quot;2020-07-09T10:01:10.450Z&quot;
        },
        {
            &quot;miscType&quot;: &quot;Comment&quot;,
            &quot;miscText&quot;: &quot;Comment body&quot;
        },
        {
            &quot;miscType&quot;: &quot;CORRECT_TYPE&quot;,
            &quot;miscText&quot;: &quot;SELECT_ME&quot;
        }
    ],
&quot;confirmationNumber&quot; : &quot;123456789qwerty&quot;,
.
.
.

I need to
SELECT 
    CLOB_COL.miscData.miscText 
WHERE
    CLOB_COL.miscData.miscType = 'CORRECT_TYPE' 
AND 
    CLOB_COL.confirmationNumber = 'xxxx';

But I am having trouble since miscData is an array of objects which all contain miscType and miscData keys and I can't guarantee the array will be the same size, or in the same order.
After surviving the ordeal of installing Oracle 12c on Devuan, I came up with this query using json_exists:
select
  t.id,
  t.data.miscData,
  t.data.miscData.miscType,
  t.data.confirmationNumber
  from json_documents t
  where json_exists(
          t.data,
          '$.miscData?(@.miscType==&quot;CORRECT_TYPE&quot;)'
        )
    and json_exists(
          t.data,
          '$?(@.confirmationNumber==&quot;123456789qwerty&quot;)'
        )

FYI:
CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json CHECK (data IS JSON (STRICT))
)