Hi all,
I have been quite successful in getting data into Mautic but I can not get it back out and into Parabola.
I can link to it, and I can get the contacts, but the work around for the JSON Flattener etc no longer works and Mautic is trying to bring back over 5000 columns in a set.
Does anyone know what I am doing wrong?
Hi Tony,
Happy to help! Did you try using the Expand JSON step? It can sometimes be helpful to use the Clean Data step before expanding your JSON to escape any characters making it more difficult to be flattened.
If that does not work, would you mind please replying with a screenshot of the problem that you run into when trying that step? Thanks!
Hi Adam,
Thanks for the help. I tried the clean data and it did nothing to help.
My issue is that I pull back data from Mautic, it the case below 23 records and in the contacts column, all the separate columns for the contacts.
So I need to do a Json Flatten and a flip table and another import of the data and a table stack(or at least that is how I used to solve this issue) - I received this in the past from Parabola Help:
You can work around this problem by having two API Imports - one that takes care of the Header row and the other that has the row values:
First, I want you to duplicate your current API Import step so that you have two of them
For one of them (where the top level key is NOT selected), I want you to add a JSON Flattener step next to it and flatten the column âheadersâ. Please make sure you select âheadersâ in the dropdown to âExpand list into New Rowsâ
Then, put a Column Filter step where you select to KEEP âheaders (1)â
Then add a Table Flip step. Youâll see that this creates a header row
Finally add another Column Filter step to REMOVE âheaders (1)â
For the second API Import step, select ârowsâ for the top level key
Finally, use a Table Merge step. Connect the first branch where weâve created the header row first and then the second API Import. Now, youâll see that the Header row and the row values are neatly stacked on top of each other.
However, If I follow the above,
Above is step 2
then step 3
So,
I just cannot seem to get a set of columns with the data. If i do get further than the above, it says there are more than 5000 columns. Each record has 550 columns.
I just get nowhere with this!!!
Tony
Hi Tony,
It appears that the JSON coming from the API is structured differently now than it was when our team provided that advice. In the screenshot you previously sent us, I can see columns titled âheadersâ and ârowsâ, each containing JSON data. It now seems like headers and rows are being packaged in âcontactsâ, which should (hopefully) eliminate the need for multiple API import steps to bring in one contact.
Do you mind specifying exactly what your desired end result looks like?
In your Expand JSON step above, I can see that the JSON has been flattened into 1 row and 2,641 columns. After the Flip Data step, this should be 2,640 rows and 2 columns. Is this what you were hoping for?
Finally, have you considered using multiple API steps, and bringing in 1 âcontactsâ object per API pull? Once you pull in, expand, and flip the data (which should be under 5000 rows if you do them one by one), you could then use a Stack Tables step to combine the data all into one larger sheet.
If you have API documentation that youâre able to pass along, that would also be helpful!
Hi Adam,I will update later although the solution you talk about is ok on this test of data. Once I pull all data, there can be half a million records and this I reach the 5000 column limits etc.
Ideally I just want a table of data. Each record separate line with columns.
I had assumed it would be fairly straightforward, nothing is with Mautic.
I will update with API link soon.
Thanks for the help
Tony
Thanks for sending that over, Tony! We took a look at this on our end, and it does not seem like our Expand JSON step is able to parse this data in a user-friendly way stemming from the JSON format returned by the API.
We are going to continue to look at this on our end and will keep you updated with what we find. Appreciate your patience, and please let us know any progress youâre able to make in the meantime!
Hey @Tony_Standing,
Thanks for your patience here. Hereâs a workaround that should help parse your data so that each contact exists in a separate row.
The main issue is that the API returned multiple contact objects, but they were not formatted in an array. This workaround will reformat your JSON with a single, top-level contact and put each contact in the array.
Feel free to copy and paste this snippet into your flow to copy the step and configuration settings pictured above:
parabola:cb:1d0c6f07411b4f19ab076f5cf9329754
Just be sure to add in your authorized API step, and you should be good. Hope that helps!
Hi Daniel,
Many thanks for the help and yes that does indeed work to a fashion. If I remove the email filter the number of records increases and thus the find and replace no longer works as the value has changed in the data that is brought back. As the data and the data set is always expanding and contracting dependent on the number of records, I would need to make the find and replace a dynamic value, otherwise it will have to be manually changed every time the flow is run.
I hope that makes sense?
Changes to the URL parameters have effected the figures in the TOTAL.
Can the find and replace on the 9032 be made dynamic for whatever is in the Total?
Best wishes
Tony
Hey @Tony_Standing ,
That makes sense. Try using this snippet instead:
parabola:cb:9baeaecdc3a4456b933376a26a16fc99
Instead of using the âFind and replaceâ step, the above snippet will separate your JSON body from the top-level key into a new column. From there, we can add another text column to dynamically merge the value in your total column to create a new key.
Lastly, we can merge the columns back together to get a formatted JSON blob.
Let me know if that helps!
Hi Daniel,
Many thanks again for all of your help. It seems it does not work. In the last step I get an error:
There is no Formatted Json 23?
bw,
Tony
Hi @Tony_Standing,
Ah, nice catch. This is likely a result of how the regex step is targeting the starting key for each object. Right now, it will target ids with exactly 4 numbers like "9032":.
This updated snippet has a better regex expression to capture all object ids (regardless of how many digits) and nests them into an array:
parabola:cb:d69f63bb96cc4db1be114006af009ddc
Let me know if that helps.
