Question 108856 in 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

JOLT Transform – Nested Json to Flat Json with multiple rows

I am attempting to write a Jolt transformation specification to convert nested JSON to flat JSON with rows. The goal is to create a single spec that can handle both Input Data 1 and Input Data 2.

Input Data 1

{
  "Id": "123",
  "Name": "tets",
  "records": [
    {
      "aaa": "123",
      "test": [
        {
          "zzz": 987,
          "yyy": 123
        },
        {
          "zzz": 345,
          "yyy": 678
        }
      ]
    }
  ]
}

Input Data 2

[
  {
    "Id": "123",
    "Name": "tets",
    "records": [
      {
        "aaa": "123",
        "test": [
          {
            "zzz": 987,
            "yyy": 123
          },
          {
            "zzz": 345,
            "yyy": 678
          }
        ]
      }
    ]
  },
  {
    "Id": "1234",
    "Name": "tets2",
    "records": [
      {
        "aaa": "123",
        "test": [
          {
            "zzz": 987,
            "yyy": 123
          },
          {
            "zzz": 345,
            "yyy": 678
          }
        ]
      },
      {
        "aaa": "12345",
        "test": [
          {
            "zzz": 456,
            "yyy": 567
          }
        ]
      }
    ]
  }
]

I want the output for input 1

[
  {
    "Id": "123",
    "Name": "tets",
    "records.aaa": "123",
    "records.test.zzz": 987,
    "records.test.yyy": 123
  },
  {
    "Id": "123",
    "Name": "tets",
    "records.aaa": "123",
    "records.test.zzz": 345,
    "records.test.yyy": 678
  }
]

And for input 2, the output should be in the below format

[
  {
    "Id": "123",
    "Name": "tets",
    "records.aaa": "123",
    "records.test.zzz": 987,
    "records.test.yyy": 123
  },
  {
    "Id": "123",
    "Name": "tets",
    "records.aaa": "123",
    "records.test.zzz": 345,
    "records.test.yyy": 678
  },
  {
    "Id": "1234",
    "Name": "tets2",
    "records.aaa": "123",
    "records.test.zzz": 987,
    "records.test.yyy": 123
  },
  {
    "Id": "1234",
    "Name": "tets2",
    "records.aaa": "123",
    "records.test.zzz": 345,
    "records.test.yyy": 678
  },
  {
    "Id": "1234",
    "Name": "tets2",
    "records.aaa": "12345",
    "records.test.zzz": 456,
    "records.test.yyy": 567
  }
]
Tags:

2

Answers


  1. 0

    You can use the following spec

    [
  { // prepare to combine the two types of inputs 
    "operation": "shift",
    "spec": {
      "*": {
        "@": "type1",
        "Id": "input1"
      },
      "@": "type2[]", // convert to array this one as well by nesting within square breackets
      "Id": "input2"
    }
  },
  { // distinguish and pick the presented input to be processed within the next specs
    "operation": "modify-overwrite-beta",
    "spec": {
      "input1?": "@1,type1", // check the existence of the respective attributes by using ? at the end
      "input2?": "@1,type2"
    }
  },
  { // determine the independent objects of the reformed array
    "operation": "shift",
    "spec": {
      "input*": {
        "*": {
          "records": {
            "*": {
              "test": {
                "*": {
                  "@4,Id": "&5\.&3\.&1.Id",
                  "@4,Name": "&5\.&3\.&1.Name",
                  "@2,aaa": "&5\.&3\.&1.&4\.aaa",
                  "*": "&5\.&3\.&1.&4\.&2\.&"
                }
              }
            }
          }
        }
      }
    }
  },
  { // get rid of object keys
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[#2].&"
      }
    }
  }
]
    Login or Signup to reply.
  2. 0

    You can use this spec:

    [
  {
    "operation": "shift",
    "spec": {
      "@": "temp"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "temp": "MANY"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "records": {
            "*": {
              "test": {
                "*": {
                  "@(5,[&4].Id)": "[&5][&3][&1].Id",
                  "@(5,[&4].Name)": "[&5][&3][&1].Name",
                  "@(2,aaa)": "[&5][&3][&1].&4\.aaa",
                  "*": "[&5][&3][&1].&4\.&2\.&"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": ""
        }
      }
    }
  }
]

    NOTE: If you have more nested values, you should sure about how many they are, then JOLT can help you achieve your desired output. Otherwise, you can find another way with other tools or libraries.

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

Back To Top
Search