How would I add data to a new key within the same JSON?

[n1isaac]

I need to match data from another key in the same json and add a new key to the json.

For example, I start with this json:

[
  {
    "employee_id": "1",
    "employee_full_name": "John Doe",
    "reporting_to": "Jane Doe",
  },
  {
    "employee_id": "2",
    "employee_full_name": "Jane Doe",
    "reporting_to": "Bob Snow",
  }
]

I need to get the employee id of the reporting to person, for John Doe, I need to search for Jane Doe, get the ID (2) and add it back into John Doe’s json to look like this:

[
  {
    "employee_id": "1",
    "employee_full_name": "John Doe",
    "reporting_to": "Jane Doe",
    "Reporting_to_id": "2"
  },
  {
    "employee_id": "2",
    "employee_full_name": "Jane Doe",
    "reporting_to": "Bob Snow",
  }
]

I have attempted jmespath, however, I cannot seem to get it to search properly.

Any hot tips or ideas?

[MutedJam]

Hi @n1isaac, this works using JMESPath but is a bit complicated because JMESPath in itself is a bit tricky, but also because n8n nodes typically processes only a single item at once.

So you’d need to use suitable expressions referencing all relevant items. Here’s an example performing such a lookup on your example dataset:

The expression I am using here is this one:

{{ $jmespath($input.all(), "[].json | [?employee_full_name == '" + $json.reporting_to + "'] | [0].employee_id") }}

So my input object is $input.all(), which contains all incoming items on the current node. I am then using [].json to only look at the json field of each incoming item which holds all the visible non-binary data in n8n. The relevant test is [?employee_full_name == '" + $json.reporting_to + "']. This piece looks at the employee_full_name field of my incoming items, then compares it to the reporting_to field of the current item. Finally I am returning the employee_id field of the first result:

Hope this helps

[n1isaac]

WOWOWOW This is amazing! Thank you so much. This helps me immensely.