How to create a table data view in TDV with data from different levels in a JSON data source/result set

[Espen Haldorsen 3]

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

[Espen Haldorsen 3]

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.