json - 查询未返回数据的 JSON 数据 - Oracle Database 12c (12.1.0.2.0)

Question

我将 JSON 数据保存在表中并尝试解析数据。下面是一个样本记录。

下面是表格和记录。

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
        }
    ]
}

');

编写以下 SQL 以从上表中提取数据。

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;

输出没有给出嵌套列 c_message_num 的值。我究竟做错了什么？

非常感谢该主题专家的任何帮助！

标签： jsonoracleoracle12c

Answer 1

您需要小心引用字符串的文字。在您的情况下c_message_num应替换为c_MESSAGE_NUM. 所以使用这个查询：

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;

顺便说一句，再应用一个NESTED PATH也可以作为另一种方法：

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