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.
. . . "otherKeys" : "otherValues", "miscData": [ { "miscType": "date", "miscText": "2020-07-09T10:01:10.450Z" }, { "miscType": "Comment", "miscText": "Comment body" }, { "miscType": "CORRECT_TYPE", "miscText": "SELECT_ME" } ], "confirmationNumber" : "123456789qwerty", . . . 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.
