using the example data from
https://oracle-base.com/articles/19c/sql-nested-clause-instead-of-json_table-19c If I want the Address *and* the Contact Details, the intuitive thing would be to add another NESTED clause, like this
SELECT jt.*
FROM json_documents
NESTED data
COLUMNS (FirstName, LastName, Job, Active,
NESTED ContactDetails COLUMNS (Email, Phone),
NESTED Address columns (Street, City, Country, Postcode)
)
jt;
However, what I end up with is
"FIRSTNAME" "LASTNAME" "JOB" "ACTIVE" "EMAIL" "PHONE" "STREET" "CITY" "COUNTRY" "POSTCODE"
"John" "Doe" "Clerk" "true" "" "" "99 My Street" "My City" "UK" "A12 34B"
"John" "Doe" "Clerk" "true" "john.doe@example.com" "44 123 123456" "" "" "" ""
"Jayne" "Doe" "Manager" "false" "" "" "100 My Street" "My City" "UK" "A12 34B"
"Jayne" "Doe" "Manager" "false" "jayne.doe@example.com" "" "" "" "" ""
I am getting one row with the first NESTED, and another with the other NESTED. Is it because NESTED is shorthand for JSON_TABLE, so it is creating 2 JSON_TABLEs, and hence I am getting a cartesian join? And is that what I *should* expect?
For the query to work correctly when I have more than one NESTED object, I have to forgo the NESTED clause for the 2nd level nesting and explicitly set out each column and its PATH, like this
SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM json_documents j NESTED data
COLUMNS (first_name VARCHAR2(50 CHAR) PATH FirstName,
last_name VARCHAR2(50 CHAR) PATH LastName,
job VARCHAR2(10 CHAR) PATH Job,
email VARCHAR2(100 CHAR) PATH ContactDetails.Email,
phone VARCHAR2(50 CHAR) PATH ContactDetails.Phone,
street VARCHAR2(100 CHAR) PATH Address.Street,
city VARCHAR2(50 CHAR) PATH Address.City,
country VARCHAR2(100 CHAR) PATH Address.Country,
postcode VARCHAR2(50 CHAR) PATH Address.Postcode,
active VARCHAR2(5 CHAR) PATH Active) jt;
What it comes down to is why does this
SELECT jt.*
FROM json_documents
NESTED data
COLUMNS (FirstName, LastName, Job, Active,
NESTED ContactDetails COLUMNS (Email, Phone)
)
jt;
or this
SELECT jt.*
FROM json_documents
NESTED data
COLUMNS (FirstName, LastName, Job, Active,
NESTED Address columns (Street, City, Country, Postcode)
)
jt;
work correctly and give me 2 rows
but this
SELECT jt.*
FROM json_documents
NESTED data
COLUMNS (FirstName, LastName, Job, Active,
NESTED ContactDetails COLUMNS (Email, Phone),
NESTED Address columns (Street, City, Country, Postcode)
)
jt;
give me 4 rows?
Yes, this is intentional.
Sibling nested columns are unioned together. So you'll get a row for each nested attribute that exists in the document. If the nested attributes are arrays, the total number of rows returned for each parent JSON object is the sum of the elements in these arrays (not the Cartesian product).
I have to forgo the NESTED clause for the 2nd level nesting and explicitly set out each columnYou can mix and match simplified and extended syntax however you want!
To avoid the unioning effect, you can only have one nested clause. You can combine one NESTED column with others where you list the full path though. You can omit the data types for all the columns.
So you can write something like this:
SELECT *
FROM json_documents j NESTED data
COLUMNS (FirstName, LastName, Job VARCHAR2(10),
NESTED ContactDetails COLUMNS (Email, Phone),
street PATH Address.Street,
postcode PATH Address.Postcode,
active PATH Active) jt;
ID FIRSTNAME LASTNAME JOB STREET POSTCODE ACTIVE EMAIL PHONE
0BB1AD4639C5B69FE0636B00000A6B61 John Doe Clerk 99 My Street A12 34B true john.doe@example.com 44 123 123456
0BB1AD4639C6B69FE0636B00000A6B61 Jayne Doe Manager 100 My Street A12 34B false jayne.doe@example.com <null>
0BB1AD4639C7B69FE0636B00000A6B61 <null> <null> <null> <null> <null> <null> <null> <null> For more on nested paths, see this blog post from Beda Hammerschmidt; Architect for JSON here at Oracle:
https://blogs.oracle.com/database/post/the-new-sqljson-query-operators-part5-json_table-nested-path-ordinality-column