Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

API profile - get the JSON elements into one single column

Hello,

How can I dump each element of the JSON object into a column in a DB table (Snowflake) ?
For what I have seen, one must specify the columns within the rsd file , however since I am iterating through different (typeform API) calls, I would like to know if the entire JSON can be dumped instead.

example of the response:
{
"total_items": 9018,
"page_count": 361,
"items": [
{
...
},
{
...
}

]
}

3 Community Answers

Matillion Agent  

Arawan Gajajiva —

Hi Octavio -

You can parse sections of JSON in the response (ie the “items” array in your example), preserving the nested JSON elements within that section using this syntax:

<attr name="items" xs:type="string" readonly="false" other:xPath="items" other:valueFormat="delimited"/>
...
...
...
<rsb:set attr="RepeatElement" value="/" />

Since you are using Snowflake, I would additionally recommend manually creating the target table and define the column that will store this data as a VARIANT, so that you can then leverage things like the Flatten Variant component to parse out the data in the JSON or Array.

Hope this helps!
Arawan


Octavio M —

Thanks for your help Arawan. Another quick question, is there any documentation on how to create a API profile with JSON file instead of RSD?
Regards -Oc


Matillion Agent  

Kalyan Arangam —

Hi Octavio,

I am sorry I do not understand your question. Do you mean to ask if you can define the API-profile (RSD) in JSON instead of XML?

The RSD file is just a definition of how to interact with the source-api to extract data. Then parse the JSON (or XML) that’s returned, mapping JSON to a tabular structure before pushing the result into a table in snowflake.

I may have misunderstood your question, please can you elaborate further? Why would JSON be more helpful?

Best
Kalyan

Post Your Community Answer

To add an answer please login