Query on JSON data not returning data - Oracle Database 12c (12.1.0.2.0)

Question

I have JSON data saved in a table and trying to parse the data. Below is a sample record.

Below is the table and the record.

CREATE TABLE JSON_RIB_DATA
   ( ID NUMBER(8,0), 
     CREATE_DATE DATE, 
     DATA CLOB, 
     CONSTRAINT JSON_DOCUMENTS_JSON CHECK (data IS JSON (STRICT)) ENABLE, 
     CONSTRAINT JSON_DOCUMENTS_PK PRIMARY KEY (ID)
   );



insert into Json_rib_data (id,create_date,data)
   values (2,sysdate,'
{
    "c_database": "RIB_EH",
    "c_tables": "RIB_MESSAGE t0,RIB_MESSAGE_FAILURE t2",
    "c_hasMore": false,
    "c_offset": 0,
    "c_pageLimit": 1000,
    "c_count": 951,
    "c_next": null,
    "c_previous": null,
    "c_primaryKeys": [],
    "c_userInRoles": [
        "c_AdminRole",
        "c_MonitorRole"
    ],
    "c_items": [
        {
            "c_item": [
                {
                    "c_ADAPTER_CLASS_LOCATION": "rib-rms_Receiving_sub",
                    "c_ADAPTER_INSTANCE_NUMBER": 1,
                    "c_ATTEMPT_COUNT": 8,
                    "c_CUSTOM_DATA": null,
                    "c_CUSTOM_FLAG": "F",
                    "c_DELETE_PENDING": "0",
                    "c_DESCRIPTION": "javax.ejb.EJBException: Error while calling Injector Service.: Client received SOAP Fault from server: EJB Exception: : java.lang.RuntimeException: Exception calling plsql inject. Error from {call RMSSUB_RECEIVING.CONSUME(?,?,?,?)}: [E] Receipts are not allowed against orders in Worksheet or Submitted status. Order : 844467",
                    "c_ERROR_CODE": null,
                    "c_ERROR_TYPE": "SY",
                    "c_FAMILY": "Receiving",
                    "c_ID": "3006",
                    "c_IN_QUEUE": "0",
                    "c_JMS_QUEUE_ID": "jms1",
                    "c_MAX_ATTEMPTS": 8,
                    "c_MESSAGE_DATA": "abc",
                    "c_MESSAGE_NUM": 252,
                    "c_NEXT_ATTEMPT_TIME": "2020-09-22 21:33:42.0",
                    "c_PUBLISH_TIME": "2020-09-22 14:34:27.0",
                    "c_REASON_CODE": "SUB",
                    "c_RIB_MESSAGE_ID": "Receiving_pub_1|2020.09.22 14:34:27.076|1259",
                    "c_SEQ_NUMBER": 7,
                    "c_THREAD_VALUE": 1,
                    "c_TOPIC_NAME": "etReceiving",
                    "c_TYPE": "RECEIPTCRE"
                }
            ],
            "c_uri": null
        }
    ]
}

');

Wrote the below SQL to extract the data from the above table.

SELECT   t.id,
         t.create_date,
         j.c_database,
         j.c_tables,
         j.c_pageLimit,
         j.c_count,
         j.c_message_num
  FROM   JSON_RIB_DATA t,
         JSON_TABLE(t.data,
                    '$'
                    COLUMNS ( c_database    VARCHAR2(200) PATH '$.c_database',
                              c_tables      VARCHAR2(200) PATH '$.c_tables',
                              c_pageLimit   VARCHAR2(200) PATH '$.c_pageLimit',
                              c_count       NUMBER(12)    PATH '$.c_count',
                              NESTED PATH '$.c_items.c_item[*]'
                                 COLUMNS (c_message_num number(12)  PATH '$.c_message_num')
                            )
                    ) j
         where t.id = 2;

The Output doesn't give the value of the nested column c_message_num. What am I doing wrong?

Any help from the experts in this subject would be greatly appreciated!

Answer 1

You need to be careful for literals which are quoted strings. In your case c_message_num should be replaced with c_MESSAGE_NUM. So use this query :

SELECT   t.id,
         t.create_date,
         j.c_database,
         j.c_tables,
         j.c_pageLimit,
         j.c_count,
         j.c_message_num
  FROM   JSON_RIB_DATA t
 CROSS JOIN JSON_TABLE(t.data,
                    '$'
                    COLUMNS ( c_database    VARCHAR2(200) PATH '$.c_database',
                              c_tables      VARCHAR2(200) PATH '$.c_tables',
                              c_pageLimit   VARCHAR2(200) PATH '$.c_pageLimit',
                              c_count       NUMBER(12)    PATH '$.c_count',
                              NESTED PATH '$.c_items.c_item[*]'
                                 COLUMNS (c_message_num number(12) PATH '$.c_MESSAGE_NUM')
                            )
                    ) j
  WHERE t.id = 2;

Btw, applying one more NESTED PATH is also possible as an alternative way :

SELECT   t.id,
         t.create_date,
         j.c_database,
         j.c_tables,
         j.c_pageLimit,
         j.c_count,
         j.c_message_num
  FROM   JSON_RIB_DATA t
 CROSS JOIN JSON_TABLE(t.data,
                    '$'
                    COLUMNS ( c_database    VARCHAR2(200) PATH '$.c_database',
                              c_tables      VARCHAR2(200) PATH '$.c_tables',
                              c_pageLimit   VARCHAR2(200) PATH '$.c_pageLimit',
                              c_count       NUMBER(12)    PATH '$.c_count',                              
                              NESTED PATH '$.c_items[*]' COLUMNS (
                                     NESTED PATH '$.c_item[*]' COLUMNS (
                                            c_message_num NUMBER PATH '$.c_MESSAGE_NUM'
                                     )
                              )
                             )
                    ) j
 WHERE t.id = 2;

Demo