Create new JSON from Input

Questions
1

Hello all! I’ll appreciate it if someone gives me a hand with this.

My workflow is retrieving this data:

Example:

[
  {
    "order_number": "WB-104757",
    "appended_data": [
      {
        "factura": "WB-104757",
        "c_almacen": "101",
        "cc_cliente": "50995614",
        "d_categoria": "PRIMER - FIJADOR",
        "d_marca": "THE ORDINARY",
        "d_producto": "TO HIGH-ADHERENCE SILICONE PRIMER - 30ML",
        "d_proveedor": "",
        "d_sector": "IMPORTADO",
        "d_tipo_venta": "CONTADO",
        "f_factura": "19/07/2023",
        "nombre_completo": "Clarissa jimenez",
        "vr_descuento": "0",
        "vr_iva": "7025",
        "vr_neto": "44000"
      },
      {
        "factura": "WB-104757",
        "c_almacen": "101",
        "c_producto": "12286",
        "cc_cliente": "50995614",
        "d_categoria": "CONTORNO DE OJOS",
        "d_marca": "THE ORDINARY",
        "d_producto": "TO CAFFEINE SOLUTION 5 EGCG - 30ML",
        "d_proveedor": "",
        "d_sector": "IMPORTADO",
        "d_tipo_venta": "CONTADO",
        "f_factura": "19/07/2023",
        "nombre_completo": "Clarissa jimenez",
       "vr_descuento": "0",
        "vr_iva": "9101",
        "vr_neto": "57000"
      },
      {
        "factura": "WB-104757",
        "c_almacen": "101",
        "c_producto": "12288",
        "cc_cliente": "50995614",
        "d_categoria": "LIMPIADORA Y DESMAQUILLANTE",
        "d_marca": "THE ORDINARY",
        "d_producto": "TO GLYCOLIC ACID 7 TONING SOLUTION 240ML",
        "d_proveedor": "",
        "d_sector": "IMPORTADO",
        "d_tipo_venta": "CONTADO",
        "f_factura": "19/07/2023",
        "nombre_completo": "Clarissa jimenez",
       "vr_descuento": "0",
        "vr_iva": "13092",
        "vr_neto": "82000"
      }
    ]
  }
]

It retrieves 1 JSON like this for each order. As you can see, this order happens to have 3 products within. My data could have orders from 1 product up to 20 products.

My issue is that I need my data to fit into this JSON structure:

{
  "Date": "19/07/2023",
  "TotalItems": 3,
  "TotalProducts": 3,
  "TotalAmount": SUM(vr_neto of 3 items),
  "OrderNumber": "WB-104757",
  "ItemsAmount": 3,
  "DiscountsAmount": 0,
  "PreShippingAmount": 0,
  "ShippingAmount": 0,
  "TaxAmount": 0,
  "Items": [
    {
      "Quantity": 1,
      "ItemName": "TO HIGH-ADHERENCE SILICONE PRIMER - 30ML",
      "Brand": "THE ORDINARY",
      "Description": " ",
      "Price": 44000,
      "ListPrice": 44000,
      "SellingPrice": 44000,
      "ImageUrl": " ",
      "DetailUrl": " ",
      "Seller": " ",
      "Categories": [
        "PRIMER - FIJADOR"
      ]
    },
    {
      "Quantity": 1,
      "ItemName": "TO CAFFEINE SOLUTION 5 EGCG - 30ML",
      "Brand": "THE ORDINARY",
      "Description": " ",
      "Price": 57000,
      "ListPrice": 57000,
      "SellingPrice": 57000,
      "ImageUrl": " ",
      "DetailUrl": " ",
      "Seller": " ",
      "Categories": [
        "CONTORNO DE OJOS"
      ]
    },
    {
      "Quantity": 1,
      "ItemName": "TO GLYCOLIC ACID 7 TONING SOLUTION 240ML",
      "Brand": "THE ORDINARY",
      "Description": " ",
      "Price": 82000,
      "ListPrice": 82000,
      "SellingPrice": 82000,
      "ImageUrl": " ",
      "DetailUrl": " ",
      "Seller": " ",
      "Categories": [
        "LIMPIADORA Y DESMAQUILLANTE"
      ]
    }
  ],
  "User": {
    "SourceUserId": "string",
    "StoreCode": "string",
    "Email": "[email protected]",
    "SavedColumns": [
      {
        "Key": "nombre",
        "Value": "ezequiel"
      }
    ]
  }
}

It is easy to map all the fields that are outside the “Items”:[ ] array. My problem is that I don’t know how to create different JSON structures to write the orders since one order needs maybe 1 slot for 1 product but other order may need 5 slots for each product.

Thank you in advance

4

Hi @Lucas_Vargas, I had a crack at this earlier today but I don’t see how your input dataset relates to your desired output dataset. User information appears to be missing completely, there also seems to be a vr_neto value for each product, but no list price or selling price, and there is no quantity from what I can tell.

So I don’t think the transformation you have in mind would work purely based on the available data here.

In general you can use the Item Lists for operations such as splitting out a field like appended_data, and then calculating sums or counting items, and for concatenating such items again into a single array.

1 Like