Convert sample JSON to nested JSON array using JOLT Transformation

Question

I am facing a problem, transforming flat JSON to the nested JSON using jolt transformation. And I am very new to jolt Transformation. Input and output detail is given below.

My input:

[
  {
    "policyNo": 1,
    "lProdCode": 500,
    "name": "Prasad",
    "id": "10",
    "Age": "56"
  },
  {
    "policyNo": 1,
    "lProdCode": 500,
    "name": "Mahapatra",
    "id": "101",
    "Age": "56"
  },
  {
    "policyNo": 2,
    "lProdCode": 500,
    "name": "Pra",
    "id": "109",
    "Age": "56"
  },
  {
    "policyNo": 3,
    "lProdCode": 400,
    "name": "Pra",
    "id": "108",
    "Age": "56"
  },
  {
    "policyNo": 1,
    "lProdCode": 500,
    "name": "Pra",
    "id": "108",
    "Age": "56"
  }
]

expected output

[
  {
    "policyNo": 1,
    "lProdCode": 500,
    "beneficiaries": [
      {
        "name": "Prasad",
        "id": "10900629001",
        "Age": "56"
      },
      {
        "name": "Mahapatra",
        "id": "10900629001",
        "Age": "56"
      },
      {
        "name": "Pra",
        "id": "108",
        "Age": "56"
      }
    ]
  },
  {
    "policyNo": 2,
    "lProdCode": 500,
    "beneficiaries": [
      {
        "name": "Pra",
        "id": "10900629001",
        "Age": "56"
      }
    ]
  },
  {
    "policyNo": 3,
    "lProdCode": 400,
    "beneficiaries": [
      {
        "name": "Pra",
        "id": "108",
        "Age": "56"
      }
    ]
  }
]

Barbaros Özhan · Accepted Answer · 2021-07-16 13:24:58Z

1

Principally you need to group by policyNo attribute along with generating a new list(beneficiaries) for the attributes other than policyNo&lProdCode. That might be handled within a shift transformation. Then add three more steps to prune the roughnesses stems from the first transformation such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "policyNo": "@(1,policyNo).&",
        "lProdCode": "@(1,policyNo).&",
        "*": "@(1,policyNo).beneficiaries[&1].&"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "policyNo": "ONE",
        "lProdCode": "ONE"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

answered Jul 16, 2021 at 13:24

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

7 Comments

Gouri Mahapatra Over a year ago

Please let me know if any resource/URL guide me to learn JOLT JSON.

Barbaros Özhan Over a year ago

Hi @GouriMahapatra , sure, I frequently use this demo which is very helpful to develop my jolt level and this might be a good tutorial, especially the part which introducing the transformations under the Documentation subtitle. Best wishes.

Gouri Mahapatra Over a year ago

Ozhan Thanks your support

Gouri Mahapatra Over a year ago

Ozhan I want to rename "policyNo" field to p_cd and lProdCode field to l_cd. How I will modify this JOLT?

Barbaros Özhan Over a year ago

Hi @GouriMahapatra , then you need to convert "policyNo": "@(1,policyNo).&" to "policyNo": "@(1,policyNo).p_cd" and "lProdCode": "@(1,policyNo).&" to "lProdCode": "@(1,policyNo).l_cd" within the shift along with policyNo to p_cd and lProdCode to l_cd with the cardinality transformations.

|

Collectives™ on Stack Overflow

Convert sample JSON to nested JSON array using JOLT Transformation

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related