Question posted in 
{
"general": {
"product_key": "501088",
"group_subtype_id": 1,
"group_subtype_name": "Wheel Speed Sensor",
"variant_id": 6,
"variant_name": "DF22",
"rb_customer_id": 287383
},
"partnumbers": [
{
"partnumber": "F04FD009BD",
"pn_type": "Series OEM",
"mat_status": "00 - planned",
"properties": [
{"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"},
{"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"},
{"property_id":6,"property_name":"Axle","value_id":62,"value":"Front / Rear Right"},
{"property_id":7,"property_name":"Base Type - Development P/N","value_id":72,"value":"FFF"},
{"property_id":8,"property_name":"Base Type - Released P/N","value_id":73,"value":"SSS"}
]
}
],
"document_numbers": [
{"document_number":"1234569871","document_version":"05","document_type":"TCD"},
{"document_number":"0123456789","document_version":"01","document_type":"TCD"},
{"document_number":"1234569870","document_version":"05","document_type":"TCD"},
{"document_number":"1234567890","document_version":"01","document_type":"TCD"}
],
"target_markets": [
{"country_name":"Belize","iso_code":"BZ"},
{"country_name":"Central African Republic","iso_code":"CF"},
{"country_name":"Albania","iso_code":"AL"}
]
}
Need this output :
I tried using nested path , but not getting expected output
2
Answers
This is a bit complicated cause your json sample data. For 1 "general" row there is 1 "partnumbers" row having 5 "properties" rows which are transposed to columns. For the same 1 "general" row there are 4 rows for "document_numbers" and 3 rows for "target_markets" (transformed to list). Since you want 4 rows of data in your expected result you should further process (Main SQL) the data fetched (grid cte) from your JSON response.
Comments in code too…
Use JSON_TABLE() to fetch rows and columns from your JSON response string:
… create a cte (named it grid)
… Process resultset to get ridd of duplicates and to format your expected result :
Parse each of the nested JSON structures separately not using
NESTED PATHin the initialJSON_TABLEand returning the arrays usingFORMAT JSONand then you can use correlated sub-queries (usingCROSS APPLYorCROSS JOIN LATERAL) to parse the individual queries and cross join the rows in each of the arrays.The part numbers can be generated using a filter expression in the JSON path (rather than rows for each property and then pivoting). The target markets can be generated and aggregated so that a single row is always generated.
Like this:
Note:
WHERE ROWNUM > 0looks like it does nothing as it will always be true but it is used to materialise the initial query which allows you to chain the correlatedJSON_TABLEs.Which, for the sample data:
Outputs:
fiddle