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

Json – Jolt – inserting new a field in array list

I’m having issues with Jolt transformation, I need to extract values/fields from array list and add one custom field either from array I’m targeting to extract or outer list. But let me try to explain more in detailed through code.

This is my input (with only 2 fields sets in "data", originally I have more than 200 sets in "data"):

{
  "result": {
    "id": "123456789",
    "year": 2023,
    "version": "0.1",
    "country": "UK",
    "modified": "2023-11-09T00:00:00",
    "data": [
      {
        "FieldNumber1": "CompanyNumber",
        "FieldNumber2": "0.1",
        "FieldNumber3": "123456789",
        "FieldNumber4": false,
        "FieldNumber5": false,
        "FieldNumber6": 1,
        "FieldNumber7": 0
      },
      {
        "FieldNumber1": "CompanyName",
        "FieldNumber2": "0.1",
        "FieldNumber3": "FunctionsAndStrings",
        "FieldNumber4": false,
        "FieldNumber5": false,
        "FieldNumber6": 1,
        "FieldNumber7": 0
      }
    ],
    "description": null,
    "downloadNumber": "aaaaa-bbbbb-11111-22-abcabc123"
  },
  "true": null,
  "false": null
}

The result am expecting is something like this:

[
  {
    "id": "123456789",
    "FieldNumber1": "CompanyNumber",
    "FieldNumber2": "0.1",
    "FieldNumber3": "123456789",
    "FieldNumber4": false,
    "FieldNumber5": false,
    "FieldNumber6": 1,
    "FieldNumber7": 0
  },
  {
    "id": "123456789",
    "FieldNumber1": "CompanyName",
    "FieldNumber2": "0.1",
    "FieldNumber3": "FunctionsAndStrings",
    "FieldNumber4": false,
    "FieldNumber5": false,
    "FieldNumber6": 1,
    "FieldNumber7": 0
  }
]

or

[
  {
    "CompanyNumber": "123456789",
    "FieldNumber1": "CompanyNumber",
    "FieldNumber2": "0.1",
    "FieldNumber3": "123456789",
    "FieldNumber4": false,
    "FieldNumber5": false,
    "FieldNumber6": 1,
    "FieldNumber7": 0
  },
  {
    "CompanyNumber": "123456789",
    "FieldNumber1": "CompanyName",
    "FieldNumber2": "0.1",
    "FieldNumber3": "FunctionsAndStrings",
    "FieldNumber4": false,
    "FieldNumber5": false,
    "FieldNumber6": 1,
    "FieldNumber7": 0
  }
]

I would like to have only a list from "data" but with fixed "id" from the "result", before each data set.
Also in the first data set "FieldNumber1" will always be "CompanyNumber" and it will always contain the same value in "FieldNumber3" as "id" in "result". Maybe it easier to calculate it from "FieldNumber3".

I didn’t move further than removing "result" and extracting only "data" list with this easy jolt spec:

[
  {
    "operation": "shift",
    "spec": {
      "result": {
        "data": ""
      }
    }
  }
]

I need to store each set of data in the table with the first attribute "id" or "CompanyNumber" so I can join it with other tables.

Thanks a lot!!!

Tags:

2

Answers


  1. 0

    You can use the below Jolt spec

    [
  {
    "operation": "shift",
    "spec": {
      "result": {
        "data": {
          "*": {
            "@(2,id)": "[#4].&1.id",
            "*": "[#4].&1.&"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[]"
      }
    }
  }
]
    Login or Signup to reply.
  2. 0

    What you need is to loop through all indexes under the data array while picking the value of id from the level where it’s stated(eg. going two levels up the tree) within a shift transformation such as

    [
  {
    "operation": "shift",
    "spec": {
      "result": {
        "data": {
          "*": {
            "@2,id": "[#2].id", // @2, -> in order to go two levels up the tree and reach 
                                //        the level of "id"
            "*": "[#2].&"       // [#2]-> go two levels up the tree (eg. traverse : and {) 
                                //        in order to reach the level of indexes of 
                                //        the "data" array in arraywise manner           
          }
        }
      }
    }
  }
]

    the demo on the site http://jolt-demo.appspot.com/ is :

    enter image description here

    id key might be switched to CompanyNumber through use of "@2,id" : "[#2].CompanyNumber"

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

Back To Top
Search