JSON_TABLE_FUNCTIONALITY_TESTING

View All Public Scripts Login to Run Script

Script Name JSON_TABLE_FUNCTIONALITY_TESTING
Visibility Unlisted - anyone with the share link can access
Description To complete the test case, you need to run the same query on a 12.1 instance. in 12.1, the null record is not returned; in 19c as shown here, the null record in JSON is returned as a row.
Area SQL General / JSON
Contributor adamwenger
Created Thursday May 11, 2023

Statement 1

select x.pol_num, x.pol_name 
  from json_table('{data:[{"id":"abcdefg","sfid":"hijklmnop","audits":[{"policy_num":"10","policy_name":"Sally"},{"policy_num":"20","policy_name":"John"}]},' || 
                  '       {"id":"azclkjd","sfid":"sldkfjd","audits":null}]}', '$.data[ * ].audits[ * ]' 
                  columns(id for ordinality, 
                          pol_num     path '$.policy_num', 
                          pol_name    path '$.policy_name')) x


POL_NUM POL_NAME
10 Sally
20 John
 -  - 

3 rows selected.

POL_NUM	POL_NAME
10	Sally
20	John
-	-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database