Transform and Sort Array into Columns by Category

Dear n8n Community,

I want to transform a array which contains lots of data in different categories.
The result should have the CategoryName as columns and the Name list as array.

The array looks like this:

[
  {
    "equipments": [
      {
        "CategoryId": 4,
        "CategoryName": "Security",
        "Count": 0,
        "Id": 202,
        "Name": "Camera",
        "ShortName": "Cam"
      },
      {
        "CategoryId": 6,
        "CategoryName": "Entertainment",
        "Count": 0,
        "Id": 18,
        "Name": "DVD Player",
        "ShortName": "DVD"
      },
      {
        "CategoryId": 11,
        "CategoryName": "Assistant",
        "Count": 0,
        "Id": 242,
        "Name": "Smart Control",
        "ShortName": "Smart."
      },
      {
        "CategoryId": 4,
        "CategoryName": "Security",
        "Count": 0,
        "Id": 301,
        "Name": "Motion Sensor",
        "ShortName": "Motion"
      },
      {
        "CategoryId": 6,
        "CategoryName": "Entertainment",
        "Count": 0,
        "Id": 199,
        "Name": "MP3 Player",
        "ShortName": "MP3"
      },
      {
        "CategoryId": 11,
        "CategoryName": "Assistant",
        "Count": 0,
        "Id": 255,
        "Name": "Remote Control",
        "ShortName": "Remote."
      }
    ]
  }
]

and the output should looks like this:
image

Can anyone help me which this transformation?
I guess I need a function node but I am not really solid how to configure it.

Thank you very much.

Here is the data in the Workflow

Hi @prononext, sounds like you want the unique CategoryName values returned as keys in a single item and then apply some filtering to the objects in your existing equipments array. I have described my basic approach to such tasks over at Count occurrences of each item in JSON - #18 by MutedJam.

Here’s an example workflow processing your dataset:

Result:

Hope this helps!

very nice. Thank you that works like a charm.

1 Like