Question posted in Json
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 transform – how to rename field with field value?

LindenHSU
March 8, 2023
203 views
0 votes
2 Answers

I’m wondering if there is any way to rename the field by concatenating the values of JSON attributes into existing attribute names using JOLT transformation.

Suppose we have input here:

{
  "auth_id": "0000-0000-0000",
  "read_time": "2022-01-10T00:00:00.0",
  "src_name": "REQ-A001",
  "reading_a": "150.18",
  "reading_b": "12.10",
  "reading_c": "3.00",
  "note": 1
}

What I expect is to concatenate field values from auth_id and read_time before all existing field name, with a colon (:) as separator and the result would become:

Expected:

{
  "0000-0000-0000:2022-01-10T00:00:00.0:auth_id": "0000-0000-0000-0000-0000",
  "0000-0000-0000:2022-01-10T00:00:00.0:read_time": "2022-01-10T00:00:00.0",
  "0000-0000-0000:2022-01-10T00:00:00.0:src_name": "REQ-A001",
  "0000-0000-0000:2022-01-10T00:00:00.0:reading_a": "150.18",
  "0000-0000-0000:2022-01-10T00:00:00.0:reading_b": "12.10",
  "0000-0000-0000:2022-01-10T00:00:00.0:reading_c": "3.00",
  "0000-0000-0000:2022-01-10T00:00:00.0:note": 1
}

So far I’ve referred to

Khusan Sharipov’s question for concatenating field value
DSC’s digest of JOLT documentation to clarify how the operators do their job

and come up with a JOLT spec:

[
  {
    "operation": "modify-default-beta",
    "spec": {
      // concatenate field value auth_id and read_time, while using ":" as separator
      "ukey": "=concat(@(1,auth_id),':',@(1,read_time))"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        // concatenate field values before field name
        "@": "@(2,ukey):&"
      }
    }
  }
]

But I got this output:

{
  "0000-0000-0000:2022-01-10T00:00:00.0": {
    ":auth_id": "0000-0000-0000",
    ":read_time": "2022-01-10T00:00:00.0",
    ":src_name": "REQ-A001",
    ":reading_a": "150.18",
    ":reading_b": "12.10",
    ":reading_c": "3.00",
    ":note": 1
  }
}

I expect to use the JOLT spec in the JoltTransformJSON processor in Nifi.

Any help or guidance is much appreciated!

Answers

You can use the following transformation spec

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "ukey": "=concat(@(1,auth_id),':',@(1,read_time),':')"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "$": "@(0)" // reverse key-value pairs
      },
      "ukey": "&"
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=concat(@(1,ukey),@(1,&))"
    }
  },
  {
    "operation": "remove",
    "spec": {
      "ukey": ""
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "$": "@(0)" // reverse key-value pairs back
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*note": "=toInteger"
    }
  }
]

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

the alternative option, which gives the same result , uses consecutive shift transformation specs after having used the modify transformation you already have, as follows

[
  {
    "operation": "modify-default-beta",
    "spec": {
      "ukey": "=concat(@(1,auth_id),':',@(1,read_time))"
    }
  },
  { // nest the attributes under common object with "ukey" key
    "operation": "shift",
    "spec": {
      "*": {
        "@": "@(2,ukey).&"
      }
    }
  },
  {// concatenate upper object and one level inner object keys for the attributes except for "ukey"
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "&2:&1" // concatenation 
        },
        "ukey": {      // exception case
          "*": {
            "*": ""
          }
        }
      }
    }
  }
]

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

You can use this shorter spec:

[
  {
    "operation": "shift",
    "spec": {
      "*": "@(1,auth_id).@(1,read_time).&"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2:&1:&"
        }
      }
    }
  }
]

Or you can use another way like below:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "@(2,auth_id)": "keys[#2]",
        "@(2,read_time)": "keys[#2]",
        "$": "keys[#2]",
        "@": "values"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "keys": {
        "*": "=join(':',@0)"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "values": {
        "*": "@(2,keys[#1])"
      }
    }
  }
]

1, Shift operation Create keys and values separately.

2, Modify operation Concate all keys with :.

3, Shift operation Create our desired output.

Note: Please run each spec separately to understand this code better.

Please signup or login to give your own answer.

Click here to cancel reply.