Oracle中如何优化JSON_TABLE查询实现JSON数组转关系型数据？

我有一段JSON数据（作为JSON文件的一部分），想要用Oracle的json_table函数将其转换为关系型数据：

{ "Id" : "XXX000", 
        "elements":[
      {
         "product":{
            "prodName":"Car",
            "prodCode":"CR"
         },
         "components":[
            {
               "compName":"Toyota",
               "compCode":"BRND" 
            },
            {
               "compName":"Red",
               "compCode":"CLR"
            }
         ]
      },
      {
         "product":{
            "prodName":"Truck",
            "prodCode":"TRCK"
         },
         "components":[
            {
               "compName":"Dodge",
               "compCode":"BRND"
            },
            {
               "compName":"Blue",
               "compCode":"CLR" 
            }
         ]
      }
   ]}

我用了以下查询进行转换：

select id, 
       prdct,
       case when code = 'BRND' then val
       else '' 
       end as brnd,
       case when code = 'CLR' then val
       else '' 
       end as clr
from ary,
     json_table(car, '$'
                columns (
                          id path  '$.Id',
                          nested path '$.elements.product[*]' columns (
                                                                          prdct path  '$.prodName'
                                                                        ),
                          nested path '$.elements.components[*]' columns (
                                                                        val  path  '$.compName',
                                                                        code  path  '$.compCode'
                                                                       )
                        )
               );

当前结果不符合预期，预期结果应该是：

请问如何优化查询以得到预期结果？

原查询的问题在于：你分别对$.elements.product[*]和$.elements.components[*]做了独立的嵌套展开，这会导致产品和组件之间产生笛卡尔积，无法对应到正确的关联关系。

正确的做法是先遍历$.elements[*]（每个元素包含一个产品和其对应的组件列表），在这个层级下提取产品信息，再对当前元素的组件列表做嵌套展开，最后通过聚合函数将同一产品的不同组件值合并到一行。

优化后的查询如下：

select 
    id,
    prdct,
    max(case when code = 'BRND' then val end) as brnd,
    max(case when code = 'CLR' then val end) as clr
from ary,
     json_table(
         car, '$'
         columns (
             id path '$.Id',
             nested path '$.elements[*]' columns (
                 prdct path '$.product.prodName',
                 nested path '$.components[*]' columns (
                     val path '$.compName',
                     code path '$.compCode'
                 )
             )
         )
     )
group by id, prdct;

逻辑说明 #

1. 首先通过nested path '$.elements[*]'遍历每个产品条目，确保每个产品和其下属的组件是关联的；
2. 在每个elements条目下，提取产品名称prdct，再嵌套遍历该产品的components数组，获取组件名称和编码；
3. 使用MAX()聚合函数结合CASE语句，将同一产品的BRND和CLR组件值分别聚合到对应的列中，最终得到预期的一行对应一个产品的结构。

内容的提问来源于stack exchange，提问作者samg