Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Flatten a Complex JSON when there are 2 nested arrrays

I am facing a problem, transforming a very complex nested JSON using jolt transformation. Input and output detail is given below.

I was trying to create a jolt spec but not getting it. Could anyone please help me. Details as below:

Input JSON

{
  "key": [
    {
      "date": "27/09/2023"
    }
  ],
  "value": [
    {
      "values": [
        {
          "location": "3005",
          "total_capacity": 24,
          "capacity_flag": "",
          "orders": [
            {
              "order_type": "INIT",
              "consumed_capacity": 0
            },
            {
              "order_type": "PUSH",
              "consumed_capacity": 0
            }
          ]
        },
        {
          "location": "3007",
          "total_capacity": 72,
          "capacity_flag": "",
          "orders": [
            {
              "order_type": "INIT",
              "consumed_capacity": 0
            },
            {
              "order_type": "PUSH",
              "consumed_capacity": 0
            },
            {
              "order_type": "RPLN",
              "consumed_capacity": 0
            }
          ]
        }
      ]
    }
  ]
}

Desired Output

[
  {
    "date": "27/09/2023",
    "location": "3005",
    "total_capacity": 24,
    "capacity_flag": "",
    "order_type": "INIT",
    "consumed_capacity": 0
  },
  {
    "date": "27/09/2023",
    "location": "3005",
    "total_capacity": 24,
    "capacity_flag": "",
    "order_type": "PUSH",
    "consumed_capacity": 0
  },
  {
    "date": "27/09/2023",
    "location": "3007",
    "total_capacity": 72,
    "capacity_flag": "",
    "order_type": "INIT",
    "consumed_capacity": 0
  },
  {
    "date": "27/09/2023",
    "location": "3007",
    "total_capacity": 72,
    "capacity_flag": "",
    "order_type": "PUSH",
    "consumed_capacity": 0
  },
  {
    "date": "27/09/2023",
    "location": "3007",
    "total_capacity": 72,
    "capacity_flag": "",
    "order_type": "RPLN",
    "consumed_capacity": 0
  }
]

Jolt Spec which i tried, but date is not coming properly

[
  {
    "operation": "shift",
    "spec": {
      "value": {
        "*": {
          "values": {
            "*": {
              "orders": {
                "*": {
                  "order_type": "&3.&1.order_type",
                  "consumed_capacity": "&3.&1.consumed_capacity",
                  "@2,location": "&3.&1.location",
                  "@2,total_capacity": "&3.&1.total_capacity",
                  "@2,capacity_flag": "&3.&1.capacity_flag"
                }
              }
            }
          }
        }
      }
    }
  },
  { // get rid of the object keys generated within the previous spec
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]

Need help in writing a jolt spec so that we get a flattened Array

Tags:

2

Answers


  1. 0

    You just to have to go up again and pick up the date before including it into your output.

    Here is an idea of how you can achieve this (Note that I assume that you will always have one date)

    [
  {
    "operation": "shift",
    "spec": {
      "value": {
        "*": {
          "values": {
            "*": {
              "orders": {
                "*": {
                  "@(6,key[0].date)": "&3.&1.date",
                  "order_type": "&3.&1.order_type",
                  "consumed_capacity": "&3.&1.consumed_capacity",
                  "@2,location": "&3.&1.location",
                  "@2,total_capacity": "&3.&1.total_capacity",
                  "@2,capacity_flag": "&3.&1.capacity_flag"
                }
              }
            }
          }
        }
      }
    }
  },
  { // get rid of the object keys generated within the previous spec
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]
    Login or Signup to reply.
  2. 0

    A more dynamic and decent option might be

    [
  { // form "others" objects and "orders" arrays 
    // while extracting the value of the "date"
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&",
          "values": {
            "*": {
              "*": "&1.others.&",
              "orders": {
                "*": {
                  "*": "&3.&2[&1].&"
                }
              }
            }
          }
        }
      }
    }
  },
  { // flatten the JSON by accumulating under the common values
    // through use of identifiers "&3_&1" and "&4_&1"
    // in order to derive them from the same level
    "operation": "shift",
    "spec": {
      "*": {
        "orders": {
          "*": {
            "@3,date": { "@": "&4_&1.date" },
            "@2,others": { "*": "&4_&1.&" },
            "*": "&3_&1.&"
          }
        }
      }
    }
  },
  { // get rid of the object keys generated within the previous spec
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

    the demo on the site https://jolt-demo.appspot.com/ is :

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search