question Re: JSONtoSQL with JSON containing an array with a variable amount of values in Archives of Support Questions (Read Only)

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

Christian_Lenz — Tue, 07 Mar 2017 22:02:58 GMT

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

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

mqureshi — Tue, 07 Mar 2017 22:38:24 GMT

@Chris Lenz

This should be possible using JOLTJsonTransform processor.

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.standard.JoltTransformJSON/index.html

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.g9680451c_043

https://github.com/bazaarvoice/jolt

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

mburgess — Tue, 07 Mar 2017 22:42:41 GMT

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.

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

Christian_Lenz — Wed, 08 Mar 2017 16:59:38 GMT

Perfect answer. Worked out perfectly. Thanks a lot.

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

Christian_Lenz — Wed, 08 Mar 2017 17:00:31 GMT

Thanks. Managed to do so with the Jolt Processor.