Convert Flatten Json into array of json using JOLT

Question

I need to use Jolt to transform a flat JSON object into an array of JSON objects, where each row in the array corresponds to a unique index number from the original object. The output should have as many rows as there were index numbers in the original object.

Input JSON 1

{
  "id": "0001",
  "type": "donut",
  "name": "Cake",
  "ppu": 0.55,
  "batters.location": "test",
  "batters.batter[0].id": "1001",
  "batters.batter[0].type": "Regular",
  "batters.batter[1].id": "1002",
  "batters.batter[1].type": "Chocolate"
}

Output JSON 1

[
  {
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters.location": "test",
    "batters.batter.id": "1001",
    "batters.batter.type": "Regular"
  },
  {
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters.location": "test",
    "batters.batter.id": "1002",
    "batters.batter.type": "Chocolate"
  }
]

Another example like

Input JSON 2

{
  "[0].id": "0001",
  "[0].type": "donut",
  "[0].name": "Cake",
  "[0].ppu": 0.55,
  "[0].batters.location": "test",
  "[0].batters.batter[0].id": "1001",
  "[0].batters.batter[0].type": "Regular",
  "[0].batters.batter[1].id": "1002",
  "[0].batters.batter[1].type": "Chocolate",
  "[1].id": "0002",
  "[1].type": "donut",
  "[1].name": "Cake2",
  "[1].ppu": 0.75,
  "[1].batters.location": "test2",
  "[1].batters.batter[0].id": "1002",
  "[1].batters.batter[0].type": "Regular",
  "[1].batters.batter[1].id": "1003",
  "[1].batters.batter[1].type": "Chocolate"
}

Output JSON 2

[
  {
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters.location": "test",
    "batters.batter.id": "1001",
    "batters.batter.type": "Regular"
  },
  {
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters.location": "test",
    "batters.batter.id": "1002",
    "batters.batter.type": "Chocolate"
  },
  {
    "id": "0002",
    "type": "donut",
    "name": "Cake2",
    "ppu": 0.75,
    "batters.location": "test2",
    "batters.batter.id": "1002",
    "batters.batter.type": "Regular"
  },
  {
    "id": "0002",
    "type": "donut",
    "name": "Cake2",
    "ppu": 0.75,
    "batters.location": "test2",
    "batters.batter.id": "1003",
    "batters.batter.type": "Chocolate"
  }
]

Hi Sumit, have you checked out the previous question's answer in this while ? — Barbaros Özhan
– Barbaros Özhan, Commented Apr 14, 2023 at 7:09
Yes, I have checked and it seems that it's not possible to create a flat JSON with all types of nested JSON using the given specification. Therefore, I have decided to use the FlattenJson processor in Nifi to create a flat JSON object with an index, and then use a Jolt spec to transform it into an array of JSON objects. — Sumit Manna
– Sumit Manna, Commented Apr 14, 2023 at 7:28
For this one, seems you want to get a common solution again for both types of input. It will be hard to think a complete solution but I can recommend a spec for only one type of input ... Indeed it lightens the idea for the next one. — Barbaros Özhan
– Barbaros Özhan, Commented Apr 14, 2023 at 7:38

Mohammadreza Khedri · Accepted Answer · 2023-04-14 07:55:07Z

1

You can achieve your both desired outputs with this single JOLT spec:

[
  {
    "operation": "shift",
    "spec": {
      "\\[*\\].*\\[*\\].*": {
        "@(1,\\[&(0,1)\\]\\.id)": "[&(1,1)][&(1,3)].id",
        "@(1,\\[&(0,1)\\]\\.type)": "[&(1,1)][&(1,3)].type",
        "@(1,\\[&(0,1)\\]\\.name)": "[&(1,1)][&(1,3)].name",
        "@(1,\\[&(0,1)\\]\\.ppu)": "[&(1,1)][&(1,3)].ppu",
        "@(1,\\[&(0,1)\\]\\.batters\\.location)": "[&(1,1)][&(1,3)].batters\\.location",
        "@": "[&(1,1)][&(1,3)].&(1,2)\\.&(1,4)"
      },
      "*\\[*\\].*": {
        "@(1,id)": "[0][&(1,2)].id",
        "@(1,type)": "[0][&(1,2)].type",
        "@(1,name)": "[0][&(1,2)].name",
        "@(1,ppu)": "[0][&(1,2)].ppu",
        "@(1,batters\\.location)": "[0][&(1,2)].batters\\.location",
        "@": "[0][&(1,2)].&(1,1)\\.&(1,3)"
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": "ONE"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]

answered Apr 14, 2023 at 7:55

Mohammadreza Khedri

2,7392 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sumit Manna Over a year ago

If I want to add another nested level in json. It is not working. What needs to change in jolt spec. "[0].batters.batter[1].abc[0].zzz": "QQQ" or "[0].batters.batter[1].abc[0].zzz[0].www": "AAA"

Mohammadreza Khedri Over a year ago

@SumitManna This case has another long answer. But if you want to do it yourself you can change your code structure from "\\[*\\].*\\[*\\].*" with 4 variables up to 5 or 6 and create the desired output you want.

Sumit Manna Over a year ago

@mohemmad-reza I am using this "\\[*\\].*\\[*\\].*\\[*\\].*" . still, It is not working. Do I need to change any other value?

Mohammadreza Khedri Over a year ago

@SumitManna well done, with this change you should change all your specs. get some play with that and try to achieve your desired output

Barbaros Özhan · Accepted Answer · 2023-04-14 07:50:37Z

The main idea is to partition by square-bracketed integers in order to be able to loop within the upcoming reformed objects. The solution for the first input is

[
  {
    "operation": "shift",
    "spec": {
      "*\\[*\\]*": {
        "@1,id": "&(1,2).id", // &(1,2) represents the replacement for the 2nd asterisk for the key 1 level up from the current position
        "@1,type": "&(1,2).type",
        "@1,name": "&(1,2).name",
        "@1,ppu": "&(1,2).ppu",
        "@": "&(1,2).&(1,1)&(1,3)"
      }
    }
  },
  { // repeatitions for the outer attributes are truncated
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE"
      }
    }
  },
  { // convert the JSON to the one of type array of objects
    "operation": "shift",
    "spec": {
      "*": {
        "@": "[#2]"
      }
    }
  }
]

the similar logic might be applied to the other input with a little bit of tweaks

Collectives™ on Stack Overflow

Convert Flatten Json into array of json using JOLT

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related