ORDS, SODA & JSON in the Database

JsonExample.txt

Hello,

I am trying to convert this json into a table using json_table. I almost have it all set, but two arrays in the JSON are not merging with each other and the 2nd nested path section is null for the values in the first nested path, and vice versa.

Attached is a text file with a snippit of the JSON file. Below is a screenshot of my query result. As you can see, the first nested path has values in rows where the 2nd nest is null. When the 2nd nest has actual values, the 1st nest is null. Below is my current query.

SELECT JSON_VALUE(json_document,'$.reporting_entity_name') As EntityName
,JSON_VALUE(json_document,'$.reporting_entity_type') As Type
,JSON_VALUE(json_document,'$.last_updated_on') As LastUpdated
,JSON_VALUE(json_document,'$.version') As Version,
jt.ntype, jt.name, jt.codetype, jt.billing_version, jt.billing_code, jt.description

,jt.tintype, jt.tin, jt.npi, jt.exists2
, jt.negotiated_type, jt.negotiated_rate, jt.expiration_date, jt.billing_class, jt.exists1
FROM PT_JSON c,
JSON_TABLE(c.json_document, '$.in_network[*]'
                    COLUMNS (
                              ntype varchar(500) PATH '$.negotiation_arrangement'
                              ,Name varchar(500) PATH '$.name'
                              ,codetype varchar(500) PATH '$.billing_code_type'
                              ,billing_version varchar(500) PATH '$.billing_code_type_version'
                              ,billing_code varchar(500) PATH '$.billing_code'
                              ,description varchar(500) PATH '$.description'
                              ,nested path '$.negotiated_rates.provider_groups[*]'
                              
                              columns(
                              tintype varchar(50) path '$.tin.type',
                                tin varchar(50) path '$.tin.value' ERROR ON ERROR,                           
                              npi varchar(50) path '$.npi[*]' ERROR ON ERROR,
                              exists2 number exists path '$.tin.value')
                      --        negotiated_type varchar(5000) FORMAT JSON WITH ARRAY WRAPPER path '$.negotiated_prices.negotiated_rate' ERROR ON ERROR)
                            ,nested path '$.negotiated_rates.negotiated_prices[*]'
                              columns(
                              negotiated_type varchar(50) path '$.negotiated_type',
                              negotiated_rate varchar(50) path '$.negotiated_rate',
                              expiration_date varchar(50) path '$.expiration_date',
                              billing_class varchar(50) path '$.billing_class',
                              exists1 number exists path '$.negotiated_type')
                                                         
                              
                              
                             )) jt