sql - 使用 Oracle SQL 解析 Json - JSON_TABLE

Question

我正在尝试使用 JSON_TABLE 解析 JSON。
Oracle 数据库版本 12.1.0.2

{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{
             "mName" : "Carol",
             "lName" : "Cena"
            },
            {
             "mName" : "Mark",
             "lName" : "Karlo"
            }
           ]
}

尝试使用以下查询但未获得预期结果。

select * from json_Table(
'{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{"mName" : "Carol","lName" : "Cena"},
            {"mName" : "Mark","lName" : "Karlo"}
           ]
}', 
'$' columns (  rownr number path '$.Rownum', 
               name varchar2(100) path '$.Name', 
               nested path '$.TextObj[*]' columns  (mName varchar2(100) path '$.mName',
                                                    lName varchar2(100) path '$.lName'
                                                    ),
               nested path '$.AddressArray[*]' columns(AddressArray varchar2(100) path '$')
             )
);

以上查询的输出:

预期输出:

Answer 1

您似乎想要数组值的交叉连接(按 rownum 和 name 分组)。这不是标准的 JSON 结构，因此您不应期望能够使用 json_table 的单个应用程序来执行此操作。 .
这是通过两次调用 json_table 来执行此操作的一种方法.在第一次调用中，您使用嵌套路径仅获取名称，但仍保留地址数组。在第二次调用中，您为第一次调用生成的每一行分别解压缩地址。
请注意在外部 select 中使用了优化器提示。 .这是必需的，因为没有它，优化器将尝试横向连接( outer apply )的非法“取消嵌套”，然后抛出错误，而不是保持查询原样。 (这是优化器的一个非常常见且令人讨厌的习惯:它尝试一些无效的东西，然后它会提示它。)
另外，rownum是保留关键字 - 您不能将其用作输出中的列名。 (从技术上讲，您可以通过额外的工作，但最好相信您不能。)

with
  t as (
    select * 
    from   json_Table(
'{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{"mName" : "Carol","lName" : "Cena"},
            {"mName" : "Mark","lName" : "Karlo"}
           ]
}', 
           '$' columns (
                 rownr        number                     path '$.Rownum', 
                 name         varchar2(100)              path '$.Name', 
                 addressArray varchar2(4000) format json path '$.AddressArray',
                 nested path '$.TextObj[*]'
                   columns  (mName varchar2(100) path '$.mName',
                             lName varchar2(100) path '$.lName'
                            )
               )
           )
  )
select /*+ no_query_transformation */ rownr, name, mname, lname, address
from t
     outer apply
     json_table (t.addressArray, '$[*]'
                   columns (address varchar2(10) path '$')
     )
;

输出:

ROWNR NAME   MNAME  LNAME  ADDRESS   
----- ------ ------ ------ ----------
    1 John   Carol  Cena   Address1  
    1 John   Carol  Cena   Address2  
    1 John   Mark   Karlo  Address1  
    1 John   Mark   Karlo  Address2