Transform complex Json array using Jolt

Question

When Calling an external API I am receiving a complex json structure, the same I need to transform into a simple json as per our need. I found jolt has the capability to transform a json but not able to come up with the jolt spec.

My input Json array --

{
  "attribute": [
    "teamalloc",
    "prodAlloc"
  ],
  "item": {
    "id": "abcde",
    "name": "Champak Kumar",
    "allocDetails": {
      "updatedAt": "2020-08-10T14:26:48-07:00",
      "token": 1134,
      "items": [
        {
          "allocation": 0.2,
          "team": {
            "id": 90,
            "name": "Some Team Name 1",
            "createdAt": "2010-01-19T10:52:52-07:00"
          }
        },
        {
          "allocation": 0.9,
          "team": {
            "id": 80,
            "name": "Some Team Name 2",
            "createdAt": "2010-01-19T10:52:52-07:00",
            "product": {
              "id": 20,
              "name": "Some Product Name 1",
              "otherDetails": {
                "key": "Id",
                "value": "GEC"
              }
            }
          }
        },
        {
          "allocation": 0.1,
          "team": {
            "id": 10,
            "name": "Some Team Name 3",
            "createdAt": "2010-01-19T10:52:52-07:00",
            "product": {
              "id": 22,
              "name": "Some Product Name 2",
              "otherDetails": {
                "key": "Id1",
                "value": "GEC1"
              }
            }
          }
        }
      ]
    }
  }
}

My output Json structure should look like --

{
  "name": "Champak Kumar",
  "allocDetails": [
    {
      "allocation": 0.2,
      "team": {
        "id": 90,
        "name": "Some Team Name 1"
      }
    },
    {
      "allocation": 0.9,
      "team": {
        "id": 80,
        "name": "Some Team Name 2",
        "product": {
          "id": 20,
          "name": "Some Product Name 1"
        }
      }
    },
    {
      "allocation": 0.1,
      "team": {
        "id": 10,
        "name": "Some Team Name 3",
        "product": {
          "id": 22,
          "name": "Some Product Name 2"
        }
      }
    }
  ]
}

I tried multiple jolt spec but couldn't come up with the desired output. What should be the ideal jolt spec for this?

kasptom · Accepted Answer · 2021-07-01 22:28:18Z

2

This spec should using shift operation should work:

[
  {
    "operation": "shift",
    "spec": {
      "item": {
        "name": "name",
        "allocDetails": {
          "items": {
            "*": {
              "allocation": "allocDetails[&1].allocation",
              "team": {
                "id": "allocDetails[&2].team.id",
                "name": "allocDetails[&2].team.name",
                "product": "allocDetails[&2].team.product"
              }
            }
          }
        }
      }
    }
  }
]

Edit #1:

The explanation: The shift operation spec defines where do we want to put the values from the input json in the result json.

Each key - value pair in the spec defines the source - target relation. For example (let's start from the simplest one): value from the ["item"]["name"] will land under the ["name"] key in the output JSON.
The

 "items": {
    "*": {
      ...
    }
 }

part says: "For each element of the array under the 'items' key do ..."

The ampersand operator "&X" lets us reference the key that is X levels above the LHS (the key in question). For example: Let's imagine we have the 57th element of the items array (counting from 0):

...
"name": "allocDetails[&2].team.name"
...

says: "Place the value found under the "name" key (that is in item["items"][56]["team"]["name"] in the 57th element of the array placed under the "allocDetails" key.

'*' matches the 57th element. '&2' lets us find out that the element of the array we are working on right now is the 57th element.

Have a look at the shift operation javadocs, especially at the "&" and "*" wildcards

Edit #2: considering the otherDetails comment: You could also approach it like this:

...
    "team": {
        "id": "allocDetails[&2].team.id",
        "name": "allocDetails[&2].team.name",
        "product": {
            "otherDetails": null,
            "*": "allocDetails[&3].team.product.&"
        }
    }
...

the above: puts all of the products' part keys (matched by the "*") to the key with the same name ("&") placed in the "allocDetails[&3].team.product" output json's key... with the exception of the "otherDetails" key.

edited Jul 1, 2021 at 22:28

answered Jul 1, 2021 at 18:52

kasptom

2,4682 gold badges20 silver badges23 bronze badges

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

3 Comments

PolygotP Over a year ago

Thanks for the answer. Could you please explain the spec?

PolygotP Over a year ago

Just added one more tag under product i.e. "otherDetails". What should I do in case I don't want that tag under product tag in output.. "otherDetails": { "key": "Id", "value": "GEC" }

PolygotP Over a year ago

Thanks for that.. I have also added a comment below..

PolygotP · Accepted Answer · 2021-07-01 19:38:28Z

1

Working jolt spec --

  {
    "operation": "shift",
    "spec": {
      "item": {
        "name": "name",
        "allocDetails": {
          "items": {
            "*": {
              "allocation": "allocDetails[&1].allocation",
              "team": {
                "id": "allocDetails[&2].team.id",
                "name": "allocDetails[&2].team.name",
                "product": {
                  "id": "allocDetails[&3].team.product.id",
                  "name": "allocDetails[&3].team.product.name"
                }
              }
            }
          }
        }
      }
    }
  }
]```

answered Jul 1, 2021 at 19:38

PolygotP

827 bronze badges

2 Comments

kasptom Over a year ago

I've added the explanation of the spec. I've also provided the other approach for the otherDetails part

PolygotP Over a year ago

Yes I saw that.. I was thinking in case product has more data like "otherdetails", we have to put all those as null the spec json like you have mentioned.. "product": { "otherDetails": null, "*": "allocDetails[&3].team.product.&" } Rather than this, if we mention only the required fields which we need, it will be short and future proof as well in case new data is added to the json(from 3rd party)

Collectives™ on Stack Overflow

Transform complex Json array using Jolt

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related