Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

JOlT spec help to parse JSON array

avatar

'm trying to modify big JSON file with more than 10K records. I have this kind of input and I need to make jolt transformation for each record :

 

[
      {
        "id": 1031435,
        "event_id": "Formula_257",
        "formula_id": 257,
        "ts_start": 1583164200084000,
        "ts_end": 1583164484960000,
        "type": "formula",
        "details": {
          "6aa0734f-6d6a-4b95-8a2b-2dde346f9df7": {
            "PowerActiveTriPhase": 183836912
          }
        },
        "ack_ts": null,
        "ack_user": null
      },
      {
        "id": 1031435,
        "event_id": "Formula_257",
        "formula_id": 257,
        "ts_start": 1583164200084000,
        "ts_end": 1583164484960000,
        "type": "formula",
        "details": {
          "6aa0734f-6d6a-4b95-8a2b-2dde346f9df7": {
            "PowerActiveTriPhase": 183836912
          }
        },
        "ack_ts": null,
        "ack_user": null
      }
    ]

 

 

This is my spec: 

 

[ { "operation": "shift", "spec": { "id": "id", "event_id": "event_id", "formula_id": "formula_id", "ts_start": "ts_start", "ts_end": "ts_end", "type": "type", "details": { "": { "$": "equipment_id", "": { "$": "parameter", "@": "value" } } }, "ack_ts": "ack_ts", "ack_user": "ack_user" } } ]

 

It works for one record but doesn't work for >1 record and im getting null in output. 

How to fix it? 

@Shu_ashu @mburgess  

 

Thanks!  

1 REPLY 1

avatar
Expert Contributor

@kirilldemidov 
Your spec works only if your input is a json object and not json array. For your spec to work on all the records, you have to perform the same operation(your spec) on each record. This can be done as follows:

[
{
"operation": "shift",
"spec": {
"*":{
"id": "[&1].id",
"event_id": "[&1].event_id",
"formula_id": "[&1].formula_id",
"ts_start": "[&1].ts_start",
"ts_end": "[&1].ts_end",
"type": "[&1].type",
"details": {
"*": {
"$": "[&3].equipment_id",
"*": {
"$": "[&4].parameter",
"@": "[&4].value"
}
}
},
"ack_ts": "[&1].ack_ts",
"ack_user": "[&1].ack_user"
}
}
}
]

Here, "&1" means go one level up in the spec.  In [&1].id &1 points to *. '*' is the iteration step value, meaning, index of the input array. For the first record, it will be [1].id,[1].event_id,..... Similarly, &3 (or) &4 means go 3 (or) 4 levels up, so that it points to the same *.
Also, try another spec:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "details": {
          "*": {
            "$": "[&3].equipment_id",
            "*": {
              "$": "[&4].parameter",
              "@": "[&4].value"
            }
          }
        },
        "*": "[&1].&0"
      }
    }
  }
]

The * after "details" ends refers to all the keys in the input json other than "details" key. For further clarifications, refer to https://community.cloudera.com/t5/Community-Articles/Jolt-quick-reference-for-Nifi-Jolt-Processors/t...