Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Value in nested array, nested object

cc13comSep 2 2024

Hello,

I have the following JSON structure in my table (column response type BLOB):

{
"statement_id": "01ef614e",
"status": {
"state": "SUCCEEDED"
},
"manifest": {
"format": "JSON_ARRAY",
"schema": {
"column_count": 1,
"columns": [
{
"name": "version",
"type_text": "BIGINT",
"type_name": "LONG",
"position": 2
}
]
},
"total_chunk_count": 1,
"chunks": [
{
"chunk_index": 0,
"row_offset": 0,
"row_count": 1
}
],
"total_row_count": 1,
"truncated": false
},
"result": {
"chunk_index": 0,
"row_offset": 0,
"row_count": 1,
"data_array": [
[
"1480",
"domain",
"51",
"2024-08-23T03:20:35.000Z",
"1234",
"55ed2c0d",
"MERGE",
"{\"command\"}",
"{\"jobName\":\"my_job_name\",\"jobOwnerId\":\"4321\",\"jobId\":\"my_job_id\",\"jobRunId\":\"my_job_run_id\",\"runId\":\"my_run_id\",\"triggerType\":\"manual\"}",
"{\"notebookId\":\"123\"}",
"0823",
"50",
"WriteSerializable",
"false",
"{\"numTargetRowsCopied\"}",
null,
"Runtime"
]
]
}
}

Now I would like to select the jobName, jobId, etc. I tried with the following select:

select d.id, jt.*
from   db_cc_test d, 
       json_table 
         ( d.response, '$' columns ( 
            nested path '$.result.data_array[*]' columns (
         jobname_full path '$[8]'
        , nested path '$[8]' columns(
            jobName path '$.jobName',
            jobID path '$.jobId'
            ) 
          )
        ) 
         ) jt

I'm getting the full object jobname_full, but jobName and jobID are null in the result. How do I get these values in the object as a single result per column?

Comments
Post Details
Added on Sep 2 2024
0 comments
260 views