Question 108854 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

Convert Flatten Json into array of json using JOLT

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"
  }
]
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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": {
      "*": {
        "*": ""
      }
    }
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search