Compare 2 array

[Larmier_Anthony]

Hello,

I get 2 tables on 2 different APIs with this kind of schema:

Array1= [
{ "id": 0,
"name": "X"
},
{ "id: 1,
"name": "Y"
},
{ "id": 2,
"name": "Z"
}]

Array2= [
"name": "X"
},
"name": "Y"
}]

My need would be when I cross Array1 with Array2, if I don’t find the same “name” then I do action U and if everything is good I do action T do you think it’s doable? thank you

[MutedJam]

Hi @Larmier_Anthony, the blog post at How to synchronize data between two systems – n8n Blog explains a great approach of comparing two data sources.

Should your values come through from your APIs in a nested field you can simply use the Item Lists node to split them out before comparing them.

[Larmier_Anthony]

Thanks!
Too bad Merge is exactly what I need! Except that I don’t have the same data as in the doc…
Me:

The doc:

I am missing the 4th field to add data to my table…

[MutedJam]

Hey @Larmier_Anthony, looks like you’re using different modes. The blog post discusses the Remove Key Matches mode to remove data already present in both sources and for which you don’t want to perform your action from my understanding.

Your screenshot suggests, however, that you’re using the Merge By Key mode which might not be what you need here.

[Larmier_Anthony]

Indeed it is not the good node that I use then …

In fact I have a table with a name field and I would like to cross with another table where there is a name field too, and if it is in it, retrieve the ID and insert it into the 1st table… .

[MutedJam]

You should be able to do this just fine from the sounds of it. Could you share the actual data structure you’re working with (= the JSON output of both of nodes before the Merge node)?

[Larmier_Anthony]

first array:

 [
{
"id": "11465",
"invoice_type": "invoice",
"amount": "773.25",
"id_status": "30",
"status_color": "1689e0",
"status_type": "4",
"_sort_status": "30",
"invoice_ref": "220419",
"customer_ref": "",
"vat": "162.38",
"vatincl": "935.63",
"saldo_vatincl": "935.63",
"description": "",
"_invoice_date": "2022-06-16",
"_invoice_duedate": "2022-07-01",
"invoice_date": "16/06/2022",
"invoice_duedate": "01/07/2022",
"due_delta": "-14",
"exported_bob": "1",
"id_customer": "1372",
"id_customer_contact": "1222",
"id_owner": "27",
"id_signer": "27",
"id_recovr": null,
"paid_amount": "0.00",
"customer_name": "CPAS de Malmedy",
"customer_code": "",
"customer_id_bob": "CPASDEMALM",
"status": "Envoyée",
"tags": null,
"tagnames": null,
"contact_name": "MartinIsabelle",
"costs": null,
"gross_margin": "773.250000",
"gross_margin_performance": "773.250000",
"worked_seconds": "42000.000000",
"costs_theo": "347.083333",
"amount_theo": "845.833333",
"performance": "91.41871924785",
"net_margin": "426.166667"
},
{
"id": "11464",
"invoice_type": "invoice",
"amount": "308.13",
"id_status": "30",
"status_color": "1689e0",
"status_type": "4",
"_sort_status": "30",
"invoice_ref": "220416",
"customer_ref": "",
"vat": "64.71",
"vatincl": "372.84",
"saldo_vatincl": "372.84",
"description": "",
"_invoice_date": "2022-06-16",
"_invoice_duedate": "2022-07-01",
"invoice_date": "16/06/2022",
"invoice_duedate": "01/07/2022",
"due_delta": "-14",
"exported_bob": "1",
"id_customer": "950",
"id_customer_contact": null,
"id_owner": "27",
"id_signer": "27",
"id_recovr": null,
"paid_amount": "0.00",
"customer_name": "Administration communale de Welkenraedt",
"customer_code": "ADMINISTR",
"customer_id_bob": "ADMINISTRA",
"status": "Envoyée",
"tags": null,
"tagnames": null,
"contact_name": null,
"costs": null,
"gross_margin": "308.130000",
"gross_margin_performance": "308.130000",
"worked_seconds": "15300.000000",
"costs_theo": "194.395000",
"amount_theo": "308.125000",
"performance": "100.00162271805",
"net_margin": "113.735000"
}
]

second array:

 [
{
"Name": "CPAS de Malmedy",
"ID": 783,
"ParentID": 0,
"OwnerID": 0,
"HasParent": false,
"HasOwner": false,
"ValueListID": 102,
"DisplayID": "783",
"HasIcon": false,
"HasAutomaticPermission": false,
"IsDeleted": false,
"ItemGUID": "{674EDF6D-FE71-4062-8596-4D0C192F59D7}"
}
]

suddenly, we loop on the first array, if we find a match between the “customer_name” of the 1st and the “Name” of the 2nd array then: we add “ID” in the 1st array…

[MutedJam]

Thx! I think this could do the job:

This logic would only let the Welkenraedt data set pass which is present in table 1, but not in table 2.

Following the Merge node you’ll be able to add it to your second database table (where the Do something here node is in the above example).

Is this what you had in mind?

[Larmier_Anthony]

Thank you very much for your help, it’s almost that, except that given that “CPAS de Malmedy” is indeed in array 2, I would like to take the “ID” of array 2 and put it in array 1 (there is also has an id field in the array 1, but we can replace the data )

[MutedJam]

So you would like to have the value 783 from the ID field of your Malmedy dataset as an ID on the Welkenraedt dataset as well?

You can use a Set node for that like so:

This node uses an expression like {{ $item(0).$node["Mock Data 2"].json["ID"] }} to read the ID from the Mock Data 2 node here, make sure to replace Mock Data 2 with the actual node name on your end.

[Larmier_Anthony]

It was the opposite that I wanted to do, but I succeeded thanks to you (it may not be the best way, and it may require a lot of resources I think because it will be 2 arrays of ~500 rows)

[MutedJam]

Well if it works it works

I am not sure I fully understand the logic behind this though. At the end you’re having Malmedy and bidule datasets using your example data in this workflow with their respective ID. This would also work using a much simpler flow:

If you also want the full data from the bottom branch you can merge it back in using one more Merge node to have both your id and your ID values:

[Larmier_Anthony]

Perfect the 2nd knot is exactly what I need!

thank you @MutedJam !

[MutedJam]

Excellent, glad to hear this helps! Have a good weekend