Merge two json objects if data match?

[nle]

I have two json lists where I first want to match data, and then if match, replace with data from another key.

json1:

[
	{
		"name": "abcd",
		"_id": "64a437767da5f6205aa0d4ef"
	},
	{
		"name": "efgh",
		"_id": "64a437767da5f6205aa0d4a8"
	},
	{
		"name": "ijkl",
		"_id": "64a437767da5f6205aa0d4sa"
	}	
]

json2:

[
  {
    "key1": [
      "abcd"
    ],
    "key2": "string",
    "key3": "string"
  },
  {
    "key1": [
      "efgh",
      "ijkl"
    ],
    "key2": "string",
    "key3": "string"
  }
]

And I want to:

1. Match $json1.name with $json2.key1 and then replace $json2.key1 string with $json1._id, and get a new json output like so:

[
  {
    "key1": [
      "64a437767da5f6205aa0d4ef"
    ],
    "key2": "string",
    "key3": "string"
  },
  {
    "key1": [
      "64a437767da5f6205aa0d4a8",
      "64a437767da5f6205aa0d4sa"
    ],
    "key2": "string",
    "key3": "string"
  }
]

Any input on how I can achieve that?

(hope I explained it good enough, if not let me know, and I’ll give it another go)

[EmeraldHerald]

Hi @nle

This was a bit complicated to figure out, but we got there - here’s an example of how you can do this with some custom code in a Code node:

Here’s the JSON output:

[nle]

Wow. Thanks a lot, it seems like you got it to work at your end.

Unfortunately, when I copy the workflow and try to execute it (without any changes) I get:

Problem running workflow
column "data" of relation "execution_entity" does not exist

I’m running n8n 0.233.1 in docker.

[EmeraldHerald]

Hi @nle

By chance, do you have Docker set to automatically update?

If so, you might want to check out this post from my teammate Jon about the issue:

Problem running workflow SQLITE_ERROR: table execution_entity has no column named data

Hey, Are you all using docker and is it set to manually update? To resolve the issue if you are not using MySQL set the version tag to 0.234.0 or 0.235.0, We found an issue issue with the MySQL migration yesterday and changed the latest version from 0.234.0 back to 0.233.1, Sadly for anyone who auto updates their containers or runs a docker pull every day they would run into this issue. 0.234.0 had a database migration in it that can’t be reversed so when it went back down to 0.233.1 it start…

[nle]

That’s most likely it. Running watchtower. Probably not too smart tbh.

Let me try and report back

[nle]

Sorry a bit late getting back here. Defining a tag worked – so I no longer got any errors.

I had some other issues to fix in the workflow, before I could implement your solution properly, but now I finally got around to it, and it seems like it works great!

Only issue must be that it avoids to throw an error, if it don’t find a match – but that’s not a big issue with this data. It was just something that popped up when using test data.