How to convert a json array to relational data in oracle

Question

I have the following json array that is a part of a json file that I am trying to convert to relational data in Oracle using the json_table function:

{ "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" 
            }
         ]
      }
   ]}

Here's the query I am using to do the conversion part:

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'
                                                                       )
                        )
               );

The results I am getting:

however, the expected results should be:

ID	PRDCT	BRND	CLR
XXX000	Car	Toyota	Red
XXX000	Truck	Dodge	Blue

How do I optimize the query to return the expected results?

Answer 1

In later Oracle versions (either Oracle 19 or 21 and later) you can use:

select id, 
       prdct,
       brnd,
       clr
from   ary
       CROSS APPLY JSON_TABLE(
         car,
         '$'
         COLUMNS (
           id PATH  '$.Id',
           NESTED PATH '$.elements[*]' COLUMNS (
             prdct PATH '$.product.prodName',
             brnd  PATH '$.components[*]?(@.compCode == "BRND").compName',
             clr   PATH '$.components[*]?(@.compCode == "CLR").compName'
           )
         )
       );

Which, for the sample data:

CREATE TABLE ary (car BLOB CHECK (car IS JSON));

INSERT INTO ary (car) VALUES ('{ "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" 
            }
         ]
      }
   ]}')

Outputs:

ID	PRDCT	BRND	CLR
XXX000	Car	Toyota	Red
XXX000	Truck	Dodge	Blue

In earlier versions, if brand is always first in the array and colour always second then you can use:

select id, 
       prdct,
       brnd,
       clr
from   ary
       CROSS APPLY JSON_TABLE(
         car,
         '$'
         COLUMNS (
           id PATH  '$.Id',
           NESTED PATH '$.elements[*]' COLUMNS (
             prdct PATH '$.product.prodName',
             brnd  PATH '$.components[0].compName',
             clr   PATH '$.components[1].compName'
           )
         )
       );

Which outputs the same (if you are assured of the array elements being in the same order).

fiddle

Answer 2

You have to return the (val, code) at same level as the product and then PIVOT.

select * from (
    select id, 
           prdct,
           val, code
    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'
                )
            )
        )
    )
)
pivot(
    max(val) for code in ('BRND' as brnd, 'CLR' as clr)
);

XXX000  Truck   Dodge   Blue
XXX000  Car     Toyota  Red