JSONtoSQL with JSON containing an array with a variable amount of values

[#1]

Hi,

I want to put the following JSON-File into a DB-table:

{  
   "Time":"03/07/2017 11:45:46.365",
   "Subscription":"1234567",
   "ServiceCodes":[  
      "SERVICE_CODE1",
      "SERVICE_CODE2",
      "SERVICE_CODE3",
      "SERVICE_CODE4"
   ]
}

The amount of servicecodes is variable, so there are JSONs, which consists 4 ServiceCodes but there are also some which just have 1 to 3 servicecodes.

It would be perfect to put it in the table having one row per Servicecode:

TIME	SUBSCRIPTION	SERVICECODE
03/07/2017 11:45:46.365	1234567	SERVICE_CODE1
03/07/2017 11:45:46.365	1234567	SERVICE_CODE2

But I don't know if this is possible with nifi.

If not, I would be ok with having it as a comma-seperated list:

TIME	SUBSCRIPTION	SERVICECODE
03/07/2017 11:45:46.365	1234567	SERVICE_CODE1, SERVICE_CODE2, SERVICE_CODE3, SERVICE_CODE4

At the moment I am using a CONVERTJSONTOSQL-Processor in Combination with a PUTSQL-Processor. This is working for TIME and SUBSCRIPTION, but SERVICECODE is always empty. In the OracleDB ServiceCode is a varchar2(250), but this could be changed.

Any suggestions how to do this?

Thanks a lot for your help.

Rgrds,

Chris

[#2]

@Chris Lenz

This should be possible using JOLTJsonTransform processor.

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.standard.JoltTransformJ...

It helps you flatten your json. Output still remains json. It's a little more complex to learn so might take a day or two to understand all of it but its very handy. You should look at both the links below:

https://docs.google.com/presentation/d/1sAiuiFC4Lzz4-064sg1p8EQt2ev0o442MfEbvrpD1ls/edit#slide=id.g9...

https://github.com/bazaarvoice/jolt

[#3]

Thanks. Managed to do so with the Jolt Processor.

[#4 · Accepted solution]

To put each of the ServiceCodes values into its own "row", you can use JoltTransformJSON with the following shift specification:

{
    "operation": "shift",
    "spec": {
      "ServiceCodes": {
        "*": {
          "@(2,Time)": "[&].Time",
          "@(2,Subscription)": "[&].Subscription",
          "@": "[&].ServiceCode"
        }
      }
    }
  }

Given your input above, it will produce the following:

[ {
  "ServiceCode" : "SERVICE_CODE1",
  "Subscription" : "1234567",
  "Time" : "03/07/2017 11:45:46.365"
}, {
  "ServiceCode" : "SERVICE_CODE2",
  "Subscription" : "1234567",
  "Time" : "03/07/2017 11:45:46.365"
}, {
  "ServiceCode" : "SERVICE_CODE3",
  "Subscription" : "1234567",
  "Time" : "03/07/2017 11:45:46.365"
}, {
  "ServiceCode" : "SERVICE_CODE4",
  "Subscription" : "1234567",
  "Time" : "03/07/2017 11:45:46.365"
} ]

This might be able to go directly into ConvertJSONToSQL, but if it doesn't, you can use SplitJSON with $[*] or $.* as the JSON Path expression, and it will divide the array up into one flow file per object in the array. Then you should be able to transform it to SQL.

[#5]

Perfect answer. Worked out perfectly. Thanks a lot.