Oracle 避免多个json遍历

大家早上好

我正在尝试使用基本上遵循此结构的JSON文档:

{
"items": [
  {
  "id": "111A",
  "someAttributes": [
    {
    "name": "anAttribute",
    "value": "A Value"
    },
    {
    "name": "aDifferentAttribute",
    "value": "A Different Value"
    }
  ],
  "someOtherAttributes": [
    {
    "name": "anotherAttribute",
    "value": "Another Value"
    },
    {
    "name": "lookAnAttribute",
    "value": "Look, another value!"
    }
  ]
]}
复制

而且，我需要回去:

ITEM_ID       ATTR_NAME                 ATTR_VALUE
------------- ------------------------- ------------------------------
111A          anAttribute               A Value
111A          anDifferentAttribute      A Different Value
111A          anotherAttribute          Another Value
111A          lookAnAttribute           Look, another value!
复制

目前，我正在通过json_table结合这些文档的两次遍历的结果，如下所示:

SELECT item_id, attr_name, attr_value
  FROM json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someAttributes[*]' COLUMNS (
      attr_name VARCHAR2 PATH '$.name',
      attr_value VARCHAR2 PATH '$.value'
    )
   )
  )
UNION ALL
SELECT item_id, attr_name, attr_value
  FROM json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
      attr_name VARCHAR2 PATH '$.name',
      attr_value VARCHAR2 PATH '$.value'
    )
   )
  );
复制

有效。但是，在这种情况下，Oracle是否正在对JSON进行两次遍历？而且，如果是这样，有没有办法在一次遍历中做同样的事情？这些文档可以相当大 (> 500mb)。我已经考虑过使用json_table链接，但是 “someAttributes” 和 “someOtherAttributes” 对象的实际内容可能非常大，通常超过32KB扩展的最大字符串大小限制。我目前的解决方案是我唯一的选择吗？

非常感谢
布莱恩

是的。对于每个union all子查询，您将再次 (完整) 扫描该表。

您可以在对json_table的一次调用中链接许多嵌套路径。但是对于每个新的嵌套，您将获得一组新的列:

create table t (
  doc_json varchar2(1000) check (doc_json is json)
);

insert into t values ('{
"items": [
  {
  "id": "111A",
  "someAttributes": [
    {
    "name": "anAttribute",
    "value": "A Value"
    },
    {
    "name": "aDifferentAttribute",
    "value": "A Different Value"
    }
  ],
  "someOtherAttributes": [
    {
    "name": "anotherAttribute",
    "value": "Another Value"
    },
    {
    "name": "lookAnAttribute",
    "value": "Look, another value!"
    }
  ]
  }
]}');
commit;

with rws as (
SELECT j.*
  FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someAttributes[*]' COLUMNS (
      attr_name1 VARCHAR2 PATH '$.name',
      attr_value1 VARCHAR2 PATH '$.value'
    ),
    NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
      attr_name2 VARCHAR2 PATH '$.name',
      attr_value2 VARCHAR2 PATH '$.value'
    )
   )
  ) j
)
  select *
  from   rws;

ITEM_ID   ATTR_NAME1            ATTR_VALUE1         ATTR_NAME2         ATTR_VALUE2            
111A      anAttribute           A Value                                           
111A      aDifferentAttribute   A Different Value                                 
111A                                    anotherAttribute   Another Value          
111A                                    lookAnAttribute    Look, another value!
复制

如果您希望所有值都出现在一组名称/值列下，这是一个问题。

幸运的是，这很容易解决，但要稍微放松一下:

with rws as (
SELECT j.*
  FROM t, json_table(doc_json, '$.items[*]' COLUMNS (
    item_id VARCHAR2 PATH '$.id',
    NESTED PATH '$.someAttributes[*]' COLUMNS (
      attr_name1 VARCHAR2 PATH '$.name',
      attr_value1 VARCHAR2 PATH '$.value'
    ),
    NESTED PATH '$.someOtherAttributes[*]' COLUMNS (
      attr_name2 VARCHAR2 PATH '$.name',
      attr_value2 VARCHAR2 PATH '$.value'
    )
   )
  ) j
)
  select item_id, attr_name, attr_value
  from   rws
  unpivot ( (attr_name, attr_value) for src in ( 
      (attr_name1, attr_value1), 
      (attr_name2, attr_value2)
    )
  );

ITEM_ID   ATTR_NAME             ATTR_VALUE             
111A      anAttribute           A Value                
111A      aDifferentAttribute   A Different Value      
111A      anotherAttribute      Another Value          
111A      lookAnAttribute       Look, another value! 
复制