Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

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


Recommended Posts

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

  • 2 weeks later...

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...