How to add information from another node to each element like a SQL LEFT JOIN

Describe the problem/error/question

Hello,

I would like to join information from multiple tables for each element of my array. In this case, I want to retrieve the user name in UserList node from his ID in the Customer object. This type of operation is easy in sql, but seems complicated in json.

Is it also possible to send the UserID to a workflow for each of the elements of an array and add the result to each element? Some fields I have to make API calls to retrieve the information.

Every time I try the merge node, I end up with the result outside the array like this

[
  {
    "UserList.Name": "Name2",
    "CustomerCard": [
      {
        "No": "Cust1",
        "Name": "Name1",
        "UserID": 21,
      },
      {
        "No": "Cust2",
        "Name": "Name2",
        "UserID": 22,
      },
      {
        "No": "Cust3",
        "Name": "Name3",
        "UserID": 21,
      }
    ]
  }
]

Please share your workflow

Share the output returned by the last node

I expect something like this :

  "CustomerCard": [
    {
      "No": "Cust1",
      "Name": "Name1",
      "UserID": 21,
      "UserList.Name": "Name2"
    },
    {
      "No": "Cust2",
      "Name": "Name2",
      "UserID": 22,
      "UserList.Name": "Name3"
    },
    {
      "No": "Cust3",
      "Name": "Name3",
      "UserID": 21,
      "UserList.Name": "Name2"
    }
  ]

Information on your n8n setup

  • n8n version: 1.62.4
  • Database (default: SQLite): SQLite
  • n8n EXECUTIONS_PROCESS setting (default: own, main): own,main
  • **Running n8n via (Docker, npm, n8n cloud, desktop app): n8n cloud **
  • Operating system: Whatever the cloud version use

Welcome to the community @Breach3r !

Tip for sharing information

Pasting your n8n workflow


Ensure to copy your n8n workflow and paste it in the code block, that is in between the pairs of triple backticks, which also could be achieved by clicking </> (preformatted text) in the editor and pasting in your workflow.

```
<your workflow>
```

That implies to any JSON output you would like to share with us.

Make sure that you have removed any sensitive information from your workflow and include dummy or pinned data with it!


To be able to merge, you need to take the items out of the arrays with Split Out node. To combine with the expected outcome, you also need to rename the key “Name” in the UserList as there is a clash in the names with “Customers” and one will override the other rather than keeping both.

1 Like

Thanks @ihortom !!

it was easier than i thought… The split out node fixed my issue.

The result :

[
  {
    "No": "Cust1",
    "Name": "Name1",
    "UserID": 21,
    "ID": 21,
    "UserListName": "User2"
  },
  {
    "No": "Cust2",
    "Name": "Name2",
    "UserID": 22,
    "ID": 22,
    "UserListName": "User3"
  },
  {
    "No": "Cust3",
    "Name": "Name3",
    "UserID": 21,
    "ID": 21,
    "UserListName": "User2"
  }
]

Here is the example of my workflow if it can help someone else :

Thanks for your help !!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.