Help with transformation

[MaaPer]

Describe the problem/error/question

I need any clue on how to get this transformation form this JSON:

[
  {
    "NIF del contacto": 77971197,
    "Concepto": "Concepto 1",
    "Precio unidad": 20
  },
  {
    "NIF del contacto": 77971197,
    "Concepto": "Concepto 2",
    "Precio unidad": 15
  },
  {
    "NIF del contacto": 31866636,
    "Concepto": "Concepto 1",
    "Precio unidad": 30
  },
  {
    "NIF del contacto": 77971197,
    "Concepto": "Concepto 3",
    "Precio unidad": 10
  }
]

TO this JSON

[
  {
    "NIF del contacto": "77971197",
    "items": [
      {
        "name": "Concepto 1",
        "subtotal": 20
      },
      {
        "name": "Concepto 2",
        "subtotal": 15
      },
      {
        "name": "Concepto 3",
        "subtotal": 10
      }
    ]
  },
  {
    "NIF del contacto": "31866636",
    "items": [
      {
        "name": "Concepto 1",
        "subtotal": 30
      }
    ]
  }
]

What is the objective)?

To read all files of one spreadsheet and the transform in the way that we can issue one single invoice per ID, no matter the number of items, because the items will be lines inside the invoice.

[Jon]

Hey @MaaPer,

I have had a bit of a play and the best approach would be to use a code node to handle this. You can use the example below to achieve what you are after.

[MaaPer]

Thanks @Jon,
I will play with that approach and let you know.

[MaaPer]

Thanks @Jon it is exactly what I need, now I can complete the code with the rest of the data that I need.

As I’m trying to learn something form the examples given, I asked to GPT to explain the code, here is for if can be useful for anybody else:

This code is used inside a node in N8N, a workflow automation tool, to transform and manipulate JSON data. Let’s go through the code step by step to understand its functionality:

1. const transformedJSON = [];

   • This line initializes an empty array called transformedJSON. This array will store the transformed JSON data.

2. const map = new Map();

   • This line creates a new Map object called map. The Map object will be used to efficiently store and retrieve data based on a key.

3. for (const item of $input.all()) {

   • This line starts a loop that iterates over each item in the $input.all() array. The $input.all() array contains the input data for the node.

4. const nif = String(item.json["NIF del contacto"]);

   • This line extracts the value of the “NIF del contacto” property from the current item’s JSON data and converts it to a string. The extracted value is stored in the nif variable.

5. if (map.has(nif)) {

   • This line checks if the map object already has an entry with the key nif. The has() method of the Map object is used to perform this check.

6. map.get(nif).items.push({ name: item.json.Concepto, subtotal: item.json["Precio unidad"] });

   • If the map object already has an entry with the key nif, this line retrieves the corresponding value using the get() method and accesses its items property. It then pushes a new object with the properties name and subtotal into the items array. The values for these properties are extracted from the current item’s JSON data.

7. } else {

   • If the map object does not have an entry with the key nif, this line is executed.

8. const newItem = { "NIF del contacto": nif, items: [{ name: item.json.Concepto, subtotal: item.json["Precio unidad"] }] };

   • This line creates a new object called newItem with the properties NIF del contacto and items. The value of the NIF del contacto property is set to the nif variable, and the items property is initialized as an array containing a single object. The values for the name and subtotal properties of this object are extracted from the current item’s JSON data.

9. transformedJSON.push(newItem);

   • This line adds the newItem object to the transformedJSON array.

10. map.set(nif, newItem);

    • This line adds a new entry to the map object with the key nif and the value newItem. This allows efficient lookup of items based on the nif key.

11. }

    • This closing brace marks the end of the else block.

12. }

    • This closing brace marks the end of the for loop.

13. return transformedJSON;

    • Finally, this line returns the transformedJSON array, which contains the transformed JSON data.

In summary, this code takes an input array of JSON objects and transforms it into a new array called transformedJSON. It groups the input objects based on the value of the “NIF del contacto” property. If multiple objects have the same “NIF del contacto” value, they are grouped together under a single object in the transformedJSON array. Each grouped object contains the “NIF del contacto” value and an array of items, where each item has a “name” and “subtotal” property extracted from the input objects. The code uses a Map object called map to efficiently check for existing entries and store references to the grouped objects.

[MaaPer]

Sorry @Jon I have one query.
Now I need to pass the exact json of the result to the following node that is an HTTP Request calling an API that will generate some invoices.
I have tried but didn’t find the expression that clone the json of the previous node using the expression box of it he HTTP node.

If I use ing the expresión box {{ $json.items }} i receive [Array: [ and the desired json if I use {{$json}}I receive [Object: and then the desire json but I don’t find the way to pass just the json…

Thanks in advance for you help.

[MaaPer]

I did one thing, after the node (magic one with transformation) I use an split in batches to pass one by one the result of it, but then I have the same problem, didn’t know how to put jus the exact info on each item.

See the picture:

[Jon]

You could try the {{ JSON.stringify( $json.items ) }} option if the value you have is exactly what the API requires.