To homepage To homepage
Solved

Nested RSD REST API

  • 26 May 2021
  • 8 replies
  • 203 views
J
Rank
Badge

I'm trying to get data from a REST api. The challenge is that the response is nested (JSON format).

I did split up the seperate fields by editing the RSD file, adding something after the xPath...

The result looks better than before but still all my answers are comma seperated in 1 field/row combination instead of 1 field 4 rows for example. It looks like this now:

How can I make sure I only get 1 anwser per value per row? (So line 1, field q_position should be seperated to 4 rows with value 1,2,3 and 4)

icon

Best answer by Thomas Lind 27 May 2021, 12:24

View original

8 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Juriaan

Is it possible for you to pull the whole JSON file you get from the call and save it in an document.json or similar?

If so you can try to connect to it with a provider and use the flattendocument option to see if you can get them all in the correct order.

How an rsd file based on that looks should explain what the json path needs to be for it to get only one value.

Thomas Lind
Rank
Userlevel 6
Badge +5

If not you will need to make an file that is only the Questions part as an individual table.

Then you may need to add an id from the main part to make it have relations to the other area.

J
Rank
Badge

I don't really understand what you mean with pulling the whole JSON file and save it into a document.

I'm using the REST 2019 connector. I'm using the Query tool for testing directly on the ODX datasource.

I experimented a bit with the following features in the connector, without result:

Misc>Datamodel: Document or Flattenedfile

Misc>Flatten Arrays: tried a column number

Misc>JSON format: JSON or JSONRows

Connection>XPath: json\pages\questions 
This returned the following error:

 

J
Rank
Badge

I just removed the fields on root level in the rsd file. The Datamodel is now Flattenedfile.

The data looks like this now. Not sure if it's correct, but via the ID I can relate it to the root fields of the file. So then 1 have 1 table without the nested fields and 1 table with ONLY the nested fields and those I can relate to each other.

 

J
Rank
Badge

In Qlik a load script for this api is automatically generated. There I can see the nested structure is like this:

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Juriaan

You can try to use those as the json paths, but the other option you presented in the previous image is the method that will most likely work with the CData provider.

J
Rank
Badge

I tried again, started over.

Entered only the base URI and set the DataModel to Relational. 

After synchronizing the following RSD files where created:

  • answers
  • choices
  • headings
  • pages
  • questions
  • rows

Some still have some nested fields, but it's more than I had. I think it only needs time to create an acceptable datamodel.

Other remaining challenges:

  • Looping through all surveyid's. The survey [id] field is part of the base URI. I have a table with all available id's, but I guess I have to write a loop in the RSD file so data of all id's are collected.
  • Pagination, it's a type "next url" with path links/next (100 per page). It's not a default option in the REST connector so I think I need to write some code in the RSD for it.

Good to know, the boolean fields in the source are read as "unknown" datatypes in TX. Resulting in empty tables in the DSA layer. This is solved by the override datatype function in ODX datasource, converting "unknown" to "bit".

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi Juriaan

The Link/next url can be called like so.
<api:set attr="DataModel" value="DOCUMENT" />
<api:set attr="JSONPath" value="$." />
<api:set attr="URI" value="https://webpage.com/api/users" />
<api:set attr="RepeatElement" value="/json/" />
<api:set attr="EnablePaging" value="TRUE" />
<api:set attr="pageurlpath" value="header:Link" />

Reply


Terms and ConditionsCookie settings