Oracle查询嵌套JSON(具有重复字段名和动态多嵌套级别)?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (664)

我应该能够在所有行中搜索字段及其值,以确定匹配的行或具有元素及其每个输入值的行。

{
"id": 1000,
"version": 1,  
"number": "123456",
"type": "Y",
"itemName": "some name1",
"listOfItems": [
{
  "id": 2000,
  "version": 2,

  "number": "234567",
  "type": "Y",
  "itemName": "some name2",

  "listOfItems": [
    {
      "id": 3000,
      "version": 3,

      "number": "345678",
      "type": "Y",
      "itemName": "some name3",

      "listOfItems": [
        {
          "id": 4000,
          "version": 4,

          "number": "456789",
          "type": "N",
          "itemName": "some name2",

          "listOfItems": [
            {
              "id": 5000,
              "version": 5,

              "number": "567890",
              "type": "N",
              "itemName": "some name3",

              "listOfItems": [                    
              ]                  
            },
            {
              "id": 6000,
              "version": 6,

              "number": "678901",
              "type": "N",
              "itemName": "some name3",

              "listOfItems": [

              ]

            }                
          ]
        }
      ]
    }
  ]
}
]
}
提问于
用户回答回答于

Oracle 12安装程序:

CREATE TABLE t (
  value CLOB
  CONSTRAINT ensure_json CHECK( value IS JSON )
);

INSERT INTO t VALUES ( '{"id":1000,"version":1,"number":"123456","type":"Y","itemName":"some name1","listOfItems":[{"id":2000,"version":2,"number":"234567","type":"Y","itemName":"some name2","listOfItems":[{"id":3000,"version":3,"number":"345678","type":"Y","itemName":"some name3","listOfItems":[{"id":4000,"version":4,"number":"456789","type":"N","itemName":"some name2","listOfItems":[{"id":5000,"version":5,"number":"567890","type":"N","itemName":"some name3","listOfItems":[]},{"id":6000,"version":6,"number":"678901","type":"N","itemName":"some name3","listOfItems":[]}]}]}]}]}' );

查询:

WITH data ( parent, id, version, "number", "type", itemName, child ) AS (
  SELECT DISTINCT
         NULL,
         j.id,
         j.version,
         j."number",
         j."type",
         j.itemName,
         j.child
  FROM   t,
         JSON_TABLE(
           t.value,
           '$'
           COLUMNS (
             id       NUMBER       PATH '$.id',
             version  VARCHAR2(20) PATH '$.version',
             "number" VARCHAR2(20) PATH '$.number',
             "type"   VARCHAR2(20) PATH '$.type',
             itemName VARCHAR2(20) PATH '$.itemName',
             child    VARCHAR2(4000) FORMAT JSON PATH '$.listOfItems'
           )
         ) j
UNION ALL
  SELECT d.id,
         k.id,
         k.version,
         k."number",
         k."type",
         k.itemName,
         k.child
  FROM   data d,
         JSON_TABLE(
           d.child,
           '$'
           COLUMNS (
             NESTED PATH '$[*]'
               COLUMNS (
                 id       NUMBER       PATH '$.id',
                 version  VARCHAR2(20) PATH '$.version',
                 "number" VARCHAR2(20) PATH '$.number',
                 "type"   VARCHAR2(20) PATH '$.type',
                 itemName VARCHAR2(20) PATH '$.itemName',
                 child    VARCHAR2(4000) FORMAT JSON PATH '$.listOfItems'
               )
           )
         ) k
  WHERE d.child IS NOT NULL
  AND   d.child <> '[]'
)
SELECT parent,
       id,
       version,
       "number",
       "type",
       itemName
FROM   data;

结果:

PARENT   ID VERSION number type ITEMNAME
------ ---- ------- ------ ---- ----------
(NULL) 1000       1 123456    Y some name1
  1000 2000       2 234567    Y some name2
  2000 3000       3 345678    Y some name3
  3000 4000       4 456789    N some name2
  4000 5000       5 567890    N some name3
  4000 6000       6 678901    N some name3
用户回答回答于

你可以创建一个表或CLOB字段并插入JSON文本

CREATE TABLE test_json(xml_json CLOB);

Oracle数据库12c允许使用FunconJSON_值并提取任何级别的值,请参见此处的文档。

https://docs.oracle.com/database/121/SQLRF/functions093.htm#SQLRF56668

SELECT JSON_VALUE(xml_json, '$.listOfItems.id') AS idList FROM test_json;

扫码关注云+社区

领取腾讯云代金券