Espen Haldorsen 3 Posted June 23, 2021 Share Posted June 23, 2021 How do you writhe the FROM clause in a SQL statementwhen you need to pull out data from different levels in a JSON result set I am able to write a working query to get information from one level, but have some issues with the FROM clause when i try to combine data from different levels in the JSON result set (different paths, see example of the FROM clauses below) Level 1 FROM clause: FROM JSON_TABLE ( Driver."output", '$.driverResponse.drivers' COLUMNS ( fornavn varchar(20) path '$.firstName', etternavn varchar(20) path '$.lastName')) Navn, Level 2 FROM clause: FROM JSON_TABLE ( Driver."output", '$.driverResponse.drivers.tachoDriverIdentification' COLUMNS ( tachoId varchar(100) path '$.driverIdentification', tachoStatus varchar(1) path '$.cardIssuingMemberState')) Tachoid, the two paths, or rowprowiders in comparison: '$.driverResponse.drivers' '$.driverResponse.drivers.tachoDriverIdentification' Example data with one structure and all the levels: {"driverResponse":{"drivers":[{"firstName":"Anne", "lastName":Olsen", "contactInfo":{}, "tachoDriverIdentification":{"driverIdentification":"123","cardIssuingMemberState":"N"}, "oemDriverIdentifications":[{"idType":"pin","oemDriverIdentification":"12345678"},{"idType":"USB","oemDriverIdentification":"23456789"}]},.......... How can this be done Link to comment Share on other sites More sharing options...
Espen Haldorsen 3 Posted July 5, 2021 Author Share Posted July 5, 2021 I found a workaround by converting the JSON response to XML and use theany-any transformation in TDV studio. In this transformation I am looping the XML file and are able to create a new flattende file structure. By looping I am able to combinde the different levelse in the JSON result set on the same line in the flattened file as a result of the any-any tranformation. Link to comment Share on other sites More sharing options...
Recommended Posts