Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Help Transforming JSON

Solved Go to solution

Help Transforming JSON

Super Collaborator

Hi,

i need to transform the input JSON below

{ "TransactionId": "-1", "Source": "ihrs", "Name": "tag-data", "Id": "126", "Records": [ { "Name": "tag-master", "PayLoad": { "file-name": "testfile.json", "plant": "1086", "collector": "testcollector", "tag-name": "testtag", "tag-description": "test desc" } }, { "Name": "tag-detail", "PayLoad": { "tag-value": "98998.55", "tag-timestamp": "2018-01-02T16:09:39.000000+00:00", "tag-quality": "100" } }, { "Name": "tag-detail", "PayLoad": { "tag-value": "91009.47", "tag-timestamp": "2018-01-02T16:09:340000000+00:00", "tag-quality": "100" } }, { "Name": "tag-detail", "PayLoad": { "tag-value": "91021.80", "tag-timestamp": "2018-01-02T16:09:41.000000+00:00", "tag-quality": "100" } } ] }

to output like below.

{ "TransactionId": "-1", "Source": "ihrs", "Name": "tag-data", "Id": "100", "Records": [ { "Name": "tag-master", "PayLoad": { "file-name": "testfile.json", "plant": "1000", "collector": "testcollector", "tag-name": "testtag", "tag-description": "test desc", "Children": [ { "Name": "tag-detail", "PayLoad": { "tag-value": "98998.55", "tag-timestamp": "2018-01-02T16:09:39.000000+00:00", "tag-quality": "100" } }, { "Name": "tag-detail", "PayLoad": { "tag-value": "98998.55", "tag-timestamp": "2018-01-02T16:09:39.000000+00:00", "tag-quality": "100" } } ] } } ] }

i need to move the details records under master as array.. basically first element of the record array (tag master)is parent to next 3 elements(tag details). i tried to use the convert record processor with JSON converters giving correct input and output schemas for files. it didnt convert. it looks like i may have to write JOLT which i need some help with. Thanks in advance!!!

attached files for reference..

input.jsonoutput.json

Regards, Sai

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Help Transforming JSON

Super Guru

Try this Chain spec in JoltTransformJSON:

[
  {
    "operation": "shift",
    "spec": {
      "Records": {
        "*": {
          "Name": {
            "tag-master": {
              "@2": "Records[&3]"
            },
            "*": {
              "@2": "Records[#3].PayLoad.Children[]"
            }
          }
        }
      },
      "*": "&"
    }
  }
]

It matches tag-master separately, puts it back into the Records array as-is, then for each tag-detail match, puts it into a Children array in the tag-master's PayLoad object.

7 REPLIES 7

Re: Help Transforming JSON

Super Guru

Try this Chain spec in JoltTransformJSON:

[
  {
    "operation": "shift",
    "spec": {
      "Records": {
        "*": {
          "Name": {
            "tag-master": {
              "@2": "Records[&3]"
            },
            "*": {
              "@2": "Records[#3].PayLoad.Children[]"
            }
          }
        }
      },
      "*": "&"
    }
  }
]

It matches tag-master separately, puts it back into the Records array as-is, then for each tag-detail match, puts it into a Children array in the tag-master's PayLoad object.

Re: Help Transforming JSON

Super Collaborator

@Matt Burgess

Thank you.

Re: Help Transforming JSON

Super Collaborator

Hi @Matt Burgess,

I have another challenge that i need to merge these smaller JSON files in to 1 file. lets say i need to merge 1000 files that i get from above JOLT in to one single json file and load in to HDFS.

how can i do that as the input files will be combined one after other using the MergeContent processor and the resulting file wont be a valid one.

any idea how to solve this.?

Regards,

Sai

Re: Help Transforming JSON

Super Collaborator

@Matt Burgess ,

i think i will try to do this as suggested by Bryan some where else..

You can use MergeContent and set the Delimiter Strategy to "Text" and then enter [ , ] for the header, demarcator, and footer respectively...i think it should work.

Re: Help Transforming JSON

Super Guru

You're probably better served with MergeRecord, that will output the merged JSON records as an array

Highlighted

Re: Help Transforming JSON

Hi @Matt Burgess,

Can you please explain how can we do this using mergerecord as I am not able to to do it..

Re: Help Transforming JSON

Super Guru

I recommend using MergeRecord before the JoltTransformJSON as the Jolt transform should be able to be applied to the whole JSON array (after your smaller JSON objects have been merged). You'll want to use a JsonTreeRecordReader and provide an Avro schema that matches your input data above. mergerecord-example.xml is an example template where I generate data similar to yours, use MergeRecord to bundle them 20 at a time, then run the Jolt spec on it, it includes the associated Avro schema and hopefully all config to get you up and going.

Don't have an account?
Coming from Hortonworks? Activate your account here