Converting a multi nested JSON structure into a single array structure

[AndrewDersley]

Description

How do I loop through a multi-nested JSON message and create a single array JSON object?

The incoming JSON structure consists of multiple networks each network consists of multiple product types that consist of multiple products.
The result needs to be a single JSON array that consists of products that contain the parent network and product type information.

Issue

The output is creating a response structure with additional array [] elements.
This is due to the way I'm execute the for loop, as it seems that it needs to be embedded in a [] element.
Am I executing the for incorrectly or is there a better way to loop through the JSON structure?

Sample Request JSON shortened for simplicity

{
    "networks": [
        {
            "id": 1,
            "description": "Carrier ABC",
            "productTypes": [
                {
                    "id": 1,
                    "description": "Airtime",
                    "products": [
                        {
                            "id": "001",
                            "minAmount": "67.00"
                        },
                        {
                            "id": "002",
                            "minAmount": "6.00"
                        }
                    ]
                },
                {
                    "id": 2,
                    "description": "Data",
                    "products": [
                        {
                            "id": "001-2",
                            "minAmount": "7.00"
                        },
                        {
                            "id": "002-2",
                            "minAmount": "5.90"
                        }
                    ]
                }
            ]
        },
        {
            "id": 2,
            "description": "Carrier 123",
            "productTypes": [
                {
                    "id": 1,
                    "description": "Airtime",
                    "products": [
                        {
                            "id": "001-3",
                            "minAmount": "267.00"
                        },
                        {
                            "id": "002-3",
                            "minAmount": "61.00"
                        }
                    ]
                },
                {
                    "id": 2,
                    "description": "Data",
                    "products": [
                        {
                            "id": "001-5",
                            "minAmount": "17.00"
                        },
                        {
                            "id": "002-5",
                            "minAmount": "52.90"
                        }
                    ]
                }
            ]
        }
    ]
}

Expected Response

{
    "products": [
        {
            "id": "001",
            "networkId": 1,
            "networkDescription": "Carrier ABC",
            "productIdentity": 1,
            "productCatTypeName": "Airtime",
            "minAmount": "67.00"
        },
        {
            "id": "002",
            "networkId": 1,
            "networkDescription": "Carrier ABC",
            "productIdentity": 1,
            "productCatTypeName": "Airtime",
            "minAmount": "6.00"
        },
        {
            "id": "001-2",
            "networkId": 1,
            "networkDescription": "Carrier ABC",
            "productIdentity": 2,
            "productCatTypeName": "Data",
            "minAmount": "7.00"
        },
        {
            "id": "002-2",
            "networkId": 1,
            "networkDescription": "Carrier ABC",
            "productIdentity": 2,
            "productCatTypeName": "Data",
            "minAmount": "5.90"
        },
        {
            "id": "001-3",
            "networkId": 2,
            "networkDescription": "Carrier 123",
            "productIdentity": 1,
            "productCatTypeName": "Airtime",
            "minAmount": "267.00"
        },
        {
            "id": "002-3",
            "networkId": 2,
            "networkDescription": "Carrier 123",
            "productIdentity": 1,
            "productCatTypeName": "Airtime",
            "minAmount": "61.00"
        },
        {
            "id": "001-5",
            "networkId": 2,
            "networkDescription": "Carrier 123",
            "productIdentity": 2,
            "productCatTypeName": "Data",
            "minAmount": "17.00"
        },
        {
            "id": "002-5",
            "networkId": 2,
            "networkDescription": "Carrier 123",
            "productIdentity": 2,
            "productCatTypeName": "Data",
            "minAmount": "52.90"
        }
    ]
}

JSLT Code

def test(node)
  if (is-array($node))
    [ {"Result": . }]
  else if (is-object($node))

    [for (array(.))
      if(.key == "networks")
       { "products":
        [for (array(.value))
          let networkId = .id
          let description = .description
          let productTypes = array(.productTypes)
          if($productTypes)
            [for($productTypes)
              let productIdentity = .id
              let productCatType =.code
              let productCatTypeName = .description
              let fixedAmnt = .fixedAmount
              let products = array(.products)
              if($products)
                [for($products)              
                  {
                    "id": .id,
                    "networkId": $networkId,
                    "networkDescription": $description,
                    "productIdentity": $productIdentity,
                    "productCatType": $productCatType,
                    "productCatTypeName": $productCatTypeName,
                    "fixedAmount": $fixedAmnt,
                    "name": .name,
                    "description": .description,
                    "typeCode": .typeCode,
                    "minAmount": .minAmount, 
                    "maxAmount": .maxAmount, 
                    "discountPercentage": .discountPercentage,
                    "retailValue": .retailvalue,
                    "pinIndicator": .pinIndicator ,
                    "smsIndicator": .smsIndicator,
                    "recharge": .recharge,
                    "payment": .payment,
                  }
              ]
            ]
          ]
       }
    ]
  else
    $node  
  
test(.)

Actual Response

[
    {
        "products": [
            [
                [
                    {
                        "id": "001",
                        "networkId": 1,
                        "networkDescription": "Carrier ABC",
                        "productIdentity": 1,
                        "productCatTypeName": "Airtime",
                        "minAmount": "67.00"
                    },
                    {
                        "id": "002",
                        "networkId": 1,
                        "networkDescription": "Carrier ABC",
                        "productIdentity": 1,
                        "productCatTypeName": "Airtime",
                        "minAmount": "6.00"
                    }
                ],
                [
                    {
                        "id": "001-2",
                        "networkId": 1,
                        "networkDescription": "Carrier ABC",
                        "productIdentity": 2,
                        "productCatTypeName": "Data",
                        "minAmount": "7.00"
                    },
                    {
                        "id": "002-2",
                        "networkId": 1,
                        "networkDescription": "Carrier ABC",
                        "productIdentity": 2,
                        "productCatTypeName": "Data",
                        "minAmount": "5.90"
                    }
                ]
            ],
            [
                [
                    {
                        "id": "001-3",
                        "networkId": 2,
                        "networkDescription": "Carrier 123",
                        "productIdentity": 1,
                        "productCatTypeName": "Airtime",
                        "minAmount": "267.00"
                    },
                    {
                        "id": "002-3",
                        "networkId": 2,
                        "networkDescription": "Carrier 123",
                        "productIdentity": 1,
                        "productCatTypeName": "Airtime",
                        "minAmount": "61.00"
                    }
                ],
                [
                    {
                        "id": "001-5",
                        "networkId": 2,
                        "networkDescription": "Carrier 123",
                        "productIdentity": 2,
                        "productCatTypeName": "Data",
                        "minAmount": "17.00"
                    },
                    {
                        "id": "002-5",
                        "networkId": 2,
                        "networkDescription": "Carrier 123",
                        "productIdentity": 2,
                        "productCatTypeName": "Data",
                        "minAmount": "52.90"
                    }
                ]
            ]
        ]
    }
]

[AndrewDersley]

Solved by changing my initial for to be [for (array(.networks))] and then did a flatten on the output. flatten(.products)

Final JSTL code

def test(node)
  if (is-array($node))
    [ {"Result": . }]
  else if (is-object($node))

       { "products":
        [for (array(.networks))
          let networkId = .id
          let description = .description
          let productTypes = array(.productTypes)
          if($productTypes)
            [for($productTypes)
              let productIdentity = .id
              let productCatType =.code
              let productCatTypeName = .description
              let fixedAmnt = .fixedAmount
              let products = array(.products)
              if($products)
                [for($products)              
                  {
                    "id": .id,
                    "networkId": $networkId,
                    "networkDescription": $description,
                    "productIdentity": $productIdentity,
                    "productCatType": $productCatType,
                    "productCatTypeName": $productCatTypeName,
                    "fixedAmount": $fixedAmnt,
                    "name": .name,
                    "description": .description,
                    "typeCode": .typeCode,
                    "minAmount": .minAmount, 
                    "maxAmount": .maxAmount, 
                    "discountPercentage": .discountPercentage,
                    "retailValue": .retailvalue,
                    "pinIndicator": .pinIndicator ,
                    "smsIndicator": .smsIndicator,
                    "recharge": .recharge,
                    "payment": .payment,
                  }
              ]
            ]
          ]
       }

  else
    $node  
  
test(.)

[AndrewDersley]

Solved by changing my JSLT code to do an initial for loop on .networks and then doing a flatten on the output(array of products). i.e flatten(.products)

Updated JSLT Code

def test(node)
  if (is-array($node))
    [ {"Result": . }]
  else if (is-object($node))

       { "products":
        [for (array(.networks))
          let networkId = .id
          let description = .description
          let productTypes = array(.productTypes)
          if($productTypes)
            [for($productTypes)
              let productIdentity = .id
              let productCatType =.code
              let productCatTypeName = .description
              let fixedAmnt = .fixedAmount
              let products = array(.products)
              if($products)
                [for($products)              
                  {
                    "id": .id,
                    "networkId": $networkId,
                    "networkDescription": $description,
                    "productIdentity": $productIdentity,
                    "productCatType": $productCatType,
                    "productCatTypeName": $productCatTypeName,
                    "fixedAmount": $fixedAmnt,
                    "name": .name,
                    "description": .description,
                    "typeCode": .typeCode,
                    "minAmount": .minAmount, 
                    "maxAmount": .maxAmount, 
                    "discountPercentage": .discountPercentage,
                    "retailValue": .retailvalue,
                    "pinIndicator": .pinIndicator ,
                    "smsIndicator": .smsIndicator,
                    "recharge": .recharge,
                    "payment": .payment,
                  }
              ]
            ]
          ]
       }

  else
    $node  
  
test(.)

[larsga]

Thanks for updating with your solution! Using flatten() is what I would have recommended.

I think dealing with this type of nested structures is the biggest current weakness of JSLT, and it's one area I'd like to improve. That's going to take some time, though.