cancel
Showing results for 
Search instead for 
Did you mean: 

Extract or Flatten JSON Array data as columns

GanguP
New Contributor III

Need help in to pulling data from the below JSON and also from the Array (Tags) below.   I also went through the documentation but unable to get to the solution.    


"entity":
{
"totalRecords":20
"_links":
{
"self":"https://HostName/incontactapi/services/v29.0/contacts?startDate=2024-01-24T00:00:00&endDate=2024-01-...","next":null,"previous":null
}
"businessUnitId":4605487
"lastPollTime":"2024-01-30T12:18:14.604Z"
"contacts":
[
{
"abandoned":false
"abandonSeconds":0.0
"acwSeconds":0.0
"agentId":null
"agentSeconds":0.0
"tags":
[
{
"tagId":561,"tagName":"DEV"
}
]
}

2 ACCEPTED SOLUTIONS

GanguP
New Contributor III

This worked perfectly fine. I do have questions though,
1) will this methodology work for multiple tags(arrays)?
2) Adding the second JSON splitter makes it easier,  I did provide a small number of columns in the sample but it has lot morefields. If we have to add 100's of fields it becomes tedious.  Is there another option or an easier way to do this?

GanguP_0-1706630157135.png

 



View solution in original post

koryknick
Employee
Employee

@GanguP - rather than specify the individual columns to retain, you can select the "Include scalar parents" checkbox.  As long as the element is a primitive datatype (i.e. not array or object), it will automatically let it pass through.

View solution in original post

8 REPLIES 8

Aleksandar_A
Contributor III

Hello @GanguP.

Would you be able to provide us a sample output, so we can better understand what you want to achieve? 

Regards,

Aleksandar.

GanguP
New Contributor III

I am trying to extract the values including the array list and load into Snowflake. Below are the fields

abandoned
abandonSeconds
acwSeconds
agentId
agentSeconds
tagid
tagname

Attached below is a sample pipeline that will flatten on tags array, so for every element in the tag array you will have a document with the above information from the parent contacts object.

Let me know if this is what you are trying to achieve.

Regards,

Aleksandar.

GanguP
New Contributor III

This worked perfectly fine. I do have questions though,
1) will this methodology work for multiple tags(arrays)?
2) Adding the second JSON splitter makes it easier,  I did provide a small number of columns in the sample but it has lot morefields. If we have to add 100's of fields it becomes tedious.  Is there another option or an easier way to do this?

GanguP_0-1706630157135.png