question Re: Help Transforming JSON in Support Questions

Help Transforming JSON

saikrishna_tara — Wed, 06 Jun 2018 23:45:10 GMT

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.json output.json

Regards, Sai

Re: Help Transforming JSON

mburgess — Thu, 07 Jun 2018 01:41:27 GMT

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

saikrishna_tara — Fri, 08 Jun 2018 03:57:57 GMT

@Matt Burgess

Thank you.

Re: Help Transforming JSON

saikrishna_tara — Tue, 12 Jun 2018 03:55:20 GMT

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

saikrishna_tara — Tue, 12 Jun 2018 04:04:11 GMT

@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

mburgess — Tue, 12 Jun 2018 20:47:51 GMT

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

Re: Help Transforming JSON

rajatpuchnanda1 — Tue, 19 Jun 2018 18:31:27 GMT

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

mburgess — Wed, 20 Jun 2018 00:36:58 GMT

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.