CODEWITHSUNDEEP
jsonplsql
Raúl Alvarado
Raúl Alvarado

Reputation: 13

How do you run a query that returns a JSON array into a PL/SQL table?

I have the following code:

select tab.*
from (SELECT '{  "name":"John",
  "age":30,
  "cars": [
    {"name":"Ford", "models":[ "Fiesta", "Focus", "Mustang"]},
    {"name":"BMW", "models":[ "320", "X3", "X5"]},
    {"name":"Fiat", "models":[ "500", "Panda"]}
  ]}' AS DATAx FROM dual),
JSON_TABLE(DATAx, '$'
       columns ("A_json" varchar2(100) path '$.age'
               ,"B_check" varchar2(100) path '$.name'
               ,"C_check" varchar2(100) path '$.cars[*].name')) tab

which shows the following table when run:

A_json: 30
B_check: John
C_check: NULL

I would like to obtain all names from the cars array. How could I get the following output instead?:

A_json: 30
B_check: John
C_check: Ford

A_json: 30
B_check: John
C_check: BMW

A_json: 30
B_check: John
C_check: Fiat

If I try replacing * for indexes (1,2,3) it works for one row only, but I am not able to get them all.

Upvotes: 1

Views: 148

Answers (1)

Shantanu Kher
Shantanu Kher

Reputation: 1054

Nested path needs to be defined to reach to 'cars--> name'. try the below given SQL -

select tab.*
from (SELECT '{  "name":"John",
  "age":30,
  "cars": [
    {"name":"Ford", "models":[ "Fiesta", "Focus", "Mustang"]},
    {"name":"BMW", "models":[ "320", "X3", "X5"]},
    {"name":"Fiat", "models":[ "500", "Panda"]}
  ]}' AS DATAx FROM dual),
JSON_TABLE(DATAx, '$'
       columns ("A_json" varchar2(100) path '$.age'
               ,"B_check" varchar2(100) path '$.name'
               ,"C_check" varchar2(100) path '$.cars.name'
               , nested path '$.cars[*]' columns( 
                            name number path '$.name'))
               ) tab

Upvotes: 0

Related Questions