使用Oracle JSON_TABLE解析Json

Question

我正在尝试使用JSON_TABLE解析JSON。

Oracle DB 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 

Answer 2

您可以链接几个json_table调用：

select j1.rnum, j1.name, j2.address, j3.mName, j3.lName
from (
  select '{
 "Rownum": "1",
 "Name": "John",
 "AddressArray":["Address1", "Address2"],
 "TextObj":[{
             "mName" : "Carol",
             "lName" : "Cena"
            },
            {
             "mName" : "Mark",
             "lName" : "Karlo"
            }
           ]
}' as str
  from dual
) t
outer apply json_table (
  t.str format json, '$'
  columns (
    rNum number path '$.Rownum',
    name varchar2(10) path '$.Name',
    addressArray varchar2(4000) format json path '$.AddressArray',
    textObj varchar2(4000) format json path '$.TextObj'
  )
) j1
outer apply json_table (
  j1.addressArray, '$[*]'
  columns (
    address varchar2(10) path '$'
  )
) j2
outer apply json_table (
  j1.textObj, '$[*]'
  columns (
    mName varchar2(10) path '$.mName',
    lName varchar2(10) path '$.lName'
  )
) j3

其中，t只是一个内联视图，可以将示例JSON作为str列提供，然后由第一个json_table处理。它获取行号和名称值，以及传递给第二个json_table的地址数组，以及传递给第三个json_table的text对象。它们从它们的数组中产生值。

RNUM | NAME | ADDRESS  | MNAME | LNAME
---: | :--- | :------- | :---- | :----
   1 | John | Address1 | Carol | Cena 
   1 | John | Address1 | Mark  | Karlo
   1 | John | Address2 | Carol | Cena 
   1 | John | Address2 | Mark  | Karlo

db<>fiddle (18c，但在12cR1上证实)。