I have the following SELECT:
SELECT *
FROM my_fingerprint_table fpt,
JSON_TABLE (msg_txt, '$'
COLUMNS (
application_id VARCHAR2(30) PATH '$.activity.id',
person_id VARCHAR2(30) PATH '$.biographicDetails.subject.identification.id',
fingerprints FORMAT JSON PATH '$.biometricDetails.fingerImageList',
NESTED PATH '$.biometricDetails.fingerImageList[0]'
COLUMNS (
capture_dt DATE PATH '$.captureDateTime'
)
)
) fp
The application_id, person_id, and capture_dt columns retrieve what I expect.
I want the fingerprints column to contain all of the JSON that is in the fingerImageList object, which happens to be a JSON array. We don't need to parse this part of the JSON in msg_txt, except to get the captureDateTime from the first member of the array - which works fine.
The JSON looks something like:
{
"activity": {
"id": "00000".
},
"biographicDetails": [
{
"subject": {
"identification": {
"id": "0000000000"
}
}
}
],
"biometricDetails": [
{
"fingerImageList": [
{
"captureDateTime": "2019-02-28T16:28:48-05:00",
"moreData": "stuff"
},
{
"captureDateTime": "2019-02-28T16:28:48-05:00",
"moreData": "stuff2"
},
{
"captureDateTime": "2019-02-28T16:28:48-05:00",
"moreData": "stuff3"
}
]
}
]
}
(Hand wrote this so syntax may be a bit off.)
But the fingerprints column is coming out as NULL. What I want is the JSON text in the fingerImageList.
Anybody know what I'm doing wrong?