Facing problem to understand repeat element and sub repeat element while pulling the data from REST API. Any document which may help in this?
One thing I found while working with support on RepeatElement and SubRepeatElement is that you must use the BackwardCompatibility setting and set it to True. It will not work properly unless you set it to true and it's not documented anywhere that I have seen.
The purpose RepeatElement and SubRepeatElement is to allow you to flatten your JSON results to get a row for each Element or SubElement in an array. You need to think about it in the context of what is the lowest level of granularity that you will need and then work your way up from there.
Take for instance this test JSON:
{
"version": "1",
"objects": [
{
"name": "Test1",
"settings": [
{
"name": "XYZ",
"id": "123"
},
{
"name": "ABC",
"id": "567"
}
]
},
{
"name": "Test2",
"settings": [
{
"name": "EFG",
"id": "456"
},
{
"name": "HIJ",
"id": "890"
}
]
}
]
}
If I wanted to flatten the results down to the setting level and get a row for each setting in each object, I would set the RepeatElement = "/objects" and SubRepeatElement = "/objects/settings".
If you just wanted a row for each object and didn't care about the settings array then you could simply set the RepeatElement = "/objects".
I hope this helps explain how RepeatElement and SubRepeatElement works. If you need more clarification don't hesitate to ask.
Wow!!!! What a granularity level explanation Bryan. Great job
Appreciate it :)
The SubRepeatElement is aggregating result into one row seperated by comma. But I want flattened result. Please help me on this
And also explain how to handle if there are more than 2 levels of nesting in JSON in Matillion API component.
First of all you need to see how the nesting is . For example repeated elements are inherited in X followed by Y followed by Z.
So you will use Repeat element for X.
Repeat sub element for X/Y.
Repeat sub element for X/Y/Z.
I guess this should work .
Let me know if it doesn't.
Eg:-
X :
{"year" : "2020"
"Y" :
{ "month" : "09"
"Z" :
{"day" : "30"
"Time" : "08:57:00 PST"
}
}
}
Here the "entities" is repeating and "groups" is sub-repeating.
<api:set attr="RepeatElement" value="/entities" />
<api:set attr="SubRepeatElement" value="/entities/groups"/>
-------------
<rsb:info title="nameMe" desc="describeMe" >
<attr name="id" xs:type="string" other:xPath="/entities/id" />
<attr name="group_name" xs:type="string" other:xPath="/entities/groups/name" />
</rsb:info
-----
The problem being group_names are fetched in a single row seperated by commas instead of flattening.
id | group_id
43897489237ykjhfka | John,Adam
56aldksfjalk | Joe,Dane
The repeating element is working but SubRepeating element is not Working as the SubRepeating element is inturn an array. How to handle these type of nested JSON?
No problem. I am glad it helped.
Hi Bryan.
I am not sure if you encountered this.
I can only assign one repeating element(which is the SubRepeatElement).
When I do this, the main fields (repeating element) is not present in the output.
So with your example, when I set the repeating element to /objects/settings, the output is the components of settings only without the objects details ("Test" etc)
Thanks,
Ryan
Just wanted to add where to the find the BackwardCompatability Option (as I just realized this myself :)). You can find it in the properties of the endpoint config and it is needed to accomplish, that all subgroups really create a new row in the row and don't get aggregated to a comma separated list. Thanks @Bryan for you perfect advice here!
Hi,
I guess you need to add one more sub repeat element <api:set attr="SubRepeatElement" value="/entities/groups/name"/> SO it will look like this
<api:set attr="RepeatElement" value="/entities" />
<api:set attr="SubRepeatElement" value="/entities/groups"/>
<api:set attr="SubRepeatElement" value="/entities/groups/name"/>
-------------
<rsb:info title="nameMe" desc="describeMe" >
<attr name="id" xs:type="string" other:xPath="/entities/id" />
<attr name="group_name" xs:type="string" other:xPath="/entities/groups/name" />
</rsb:info
OR Try this
<api:set attr="RepeatElement" value="/entities" />
<api:set attr="SubRepeatElement" value="/entities/groups/name"/>
-------------
<rsb:info title="nameMe" desc="describeMe" >
<attr name="id" xs:type="string" other:xPath="/entities/id" />
<attr name="group_name" xs:type="string" other:xPath="/entities/groups/name" />
</rsb:info
-----
Let me know if it works for you . Although I am still evolving my learning in Matillion
HI everybody
have a similar question and problem
for the detailed matillion task data
I want a
job_id task_id
so I want to flatten to the grain of task but with some data of the job so I know which tasks were running within a job
if we say repeateelement on / and subrepeateelement on /fasks/
then it puts the whole thing per job_id on 1 row and the task data is then separated with commas
but I want to have
5 rows and per task the job within the task was run
so not aggregated comma-separated on 1 column
if I say just with repeat element /rask I get the 5 rows but no information of the higher level (like job_id)
Or how does your Matillion Api Query rsd look like for summary and detailed job and task information ?
instead of flatten within the rsd I could also store the whole task into an string and store it like that into a table
and latter flatten the task within snowflake?
thx
Kind Regards
Martin
Hi,
This documentation (https://documentation.matillion.com/docs/2836045) exolains that onlu one repeat element per rsd is allowed: "Note: Only one sub repeat element is allowed per RSD file. In the example above, if there was also a credit card record for customers and each customer could have more than one credit card then multiple passes will be required."
Does anyone know how to do multiple passes on a nested JSON?
There are a handful of ways to accomplish this. I personally utilize Python or Snowflake to go multiple levels deep on nested JSON. Although there are some approaches that you can employee which use 2 or more API Profiles to process nested JSON, they always seemed super clunky to me. If I get into a situation where I need to go deeper than a single sub-element I will usually just use a Python Script to make the API call, get the results and process as many levels deep as I need.
The other method I have used is to setup the API Profile to return a full JSON response and then use the API Query component to push the response into a variant column within a Snowflake table. From there you can query the semi structured JSON data natively within Snowflake or through a component within Matillion.
Either of these options work quite well depending on the situation and need. I hope this helps.
Hi @ryan,
I wanted to mention a quick house cleaning thing if I may. For these kind of questions on thread old this this, it's best to just create a new post and may reference the old thread via a URL. A lot of times the late replies get buried and can be hard to find.
Is there a particular reason why you can't set a repeating element? I ask because Subrepeating element doesn't work without repeating element. In my example, let say object wasn't necessarily array but just a single object and the array you want to loop over is settings. You would set the repeating element to object anyway and the subrepeating element to settings. Doing this basically flattens objects and settings so that for each settings row you will get all the objects attributes on the same row similar to this:
object_name, settings_name, settings_id
Just make sure when you are defining your attributes that you set the xpath appropriately for each attribute you are exposing. If it's for object it would be something like: /object/name and settings would be /object/settings/name
Hopefully that helps. Thanks!
Hi @Bryan.
Thank you for the advice.
I am new to this and that is really helpful.
Apologies for the confusion but I can set the repeating element. However, when I set the repeating element, it is dropping the object elements. In your example:
{
"version": "1",
"objects": [
{
"name": "Test1",
"settings": [
{
"name": "XYZ",
"id": "123"
},
{
"name": "ABC",
"id": "567"
}
]
},
When I set the repeating element for the settings, instead of flattening objects and settings, I only get:
settings_id settings_name (no object_name)
I hope I showed it clearly.
Thanks,
Ryan
It sounds like you are only using RepeatElement. If you only use RepeatElement and you set it to /object/settings/, then you will only ever get settings attributes. You would have to set RepeatElement to /objects/ and SubRepeatElement to /objects/settings.
This will give you access to both object attributes and setting attributes. Hopefully that helps.
Hi @Bryan,
Thank you for the quick response.
I am just wondering how can I set the SubRepeatElement when I can only select Set Repeating Element?
There is no option to add SubRepeatElement.
I am hoping there is another way to do it. I am setting this in the API Query Profile.
Thanks,
Ryan
Ahhhh... Everything makes sense now. Yeah, the GUI doesn't have an option for this. You have to go into Advanced mode and write it yourself. The good part about the GUI that was introduced around API Profile is that it makes the easy setups a breeze. If you want to do anything advanced and semi out of the norm then you will always end up writing it in Advanced mode. We typically spend most of our time in advanced mode because inevitably we need to do something that the GUI doesn't support.
If you go into advanced mode you will see the RSB code. Basically, take the repeat element line, copy and paste it below it and change RepeatElement to SubRepeatElement along with the path. Make sure to set BackwardCompatibility to True as well.