Merge JSON together by key

Integrator.io
, , , ,
1

Hi Team,

I'm new to the platform and am having some issues manipulating the data how I want it to look.

I'm getting my initial data from a SQL database (skusByLocation), and then am doing a lookup from a CSV file (FTP) (eandata).

I want to merge skusByLocation and eandata together utilising the sku as the key. Whats the best way to do this?

Input:
{
"data": [
{
"skusByLocation": [
{
"location": "753",
"skus": [
{
"sku": "1234",
"quantity": 3
},
{
"sku": "5678",
"quantity": 3
}
]
}
],
"eandata": [
{
"sku": "1234",
"ean": "efgh"
},
{
"sku": "5678",
"ean": "abcd"
}
]
}
]
}

Desired Output:
{
"data": [
{
"skusByLocation": [
{
"location": "753",
"skus": [
{
"sku": "1234",
"quantity": 3,
"ean": "efgh"
},
{
"sku": "5678",
"quantity": 3,
"ean": "abcd"
}
]
}
]
}

Thanks.

2

@morganjenkins this could be tricky so I have a few questions:

  1. Is the CSV file on the FTP server one large file with every sku/ean or is there a file for each sku?
  2. How many skus do you have?
  3. After you get the data from your database, then do the lookup, where are you ultimately landing the data?
3

Hi @tylerlamparter,

1. The CSV is 1 large file with every sku / ean

2. Currently there are 9100 skus in the csv.

3. Ultimately the data will be sent to a REST endpoint.

4

@morganjenkins that's sort of what I was figuring. In that case, you will most likely hit the 5 MB page size limit because you're basically adding 9100 objects per record per page that is passing through that lookup step. I think your best options are:

  1. Have 2 flows. Flow #1 will pull the CSV file and upsert it into your database. Flow #2, set to run after flow 1 runs, will be what you have now, but without the lookup, and you would update the SQL query on your export to just reference the table you upserted the ean values into. Now this does require you to have write access to the database and I'm not sure if you do.
  2. Have the FTP csv as the export step on your flow, then have a lookup back to the database with a where clause of sku = sku. From there, you could filter out records that don't have a match prior to going to your rest endpoint.