topic Re: Json data extraction in Qlik Replicate

Json data extraction

suvbin — Wed, 30 Aug 2023 10:41:30 GMT

Hi ,

Is there any way to extract json column information and load into different columns in target table . i have tried using json extract function (json_extract). But it is writing null values.

Example input: '{"name":"John", "age":30, "car":null}'

output should be :

3 columns in table

Name: John

Age: 30

Car: Null

Thanks in advance.

Re: Json data extraction

OritA — Thu, 31 Aug 2023 07:43:40 GMT

Hi,

Could you please inform us to which product are you refering?

Could you please elaborate what are you trying to achieve in Replicate by extracting column information?

Thanks & regards,

Orit

Re: Json data extraction

sureshkumar — Thu, 31 Aug 2023 08:34:35 GMT

Hello @suvbin

Please refer below white paper documentation for json data extract

Regards,
Suresh

Re: Json data extraction

Heinvandenheuvel — Sat, 02 Sep 2023 21:23:14 GMT

Replicate does not have have an option for JSON to Columns out of the box. The PDF in an other reply suggests QLIK COMPOSE as second step, but that's a larger management / corporate direction decision.

What is your source situation?

Is this flat file with Json data?
If this JSON data in TEXT column
Is this JSON data in a CLOB?

It is probably easiest to use a target DB trigger to take the JSON, Parse and Apply to those columns with data. Or a post-processing repeating process on the target (every minute? Hour?) to do such for row marked with 'unprocessed JSON' using a flag column (set by Replicate?) or just any non-nulll JSON and set JSON to NULL once processed.

If the JSON is in a text column from the source, then you could consider a User Defined Function (UDF) Transformation expression with a procedure you write with the JSON as main input and the name of a selected column as second input. Call that UDF for each column with is anticipated. Such UDF is not rocket science, but not a trivial skill either. You are likely to need consulting help for this. It may not be worth the effort, and for the versions of Replicate I worked with UDF functions did not accept BLOBs as input.

Hein.

If it is JSON data in a

Re: Json data extraction

john_wang — Sun, 03 Sep 2023 04:30:38 GMT

Hello @suvbin ,

I totally agree with @Heinvandenheuvel . Because JSON/BSON has become more popular many databases itself support JSON/BSON operations including parsing JSON document to relational like structure. Besides Hein's comment, I'd like to share a sample mongodb to mysql How to give Structure To My Data.

Hope this helps.

Regards,

John.

Re: Json data extraction

suvbin — Sun, 03 Sep 2023 05:29:06 GMT

Hi @Heinvandenheuvel ,

Thank you for the reply. Can you please provide any example for this. Actually in the db we have json data. and we need make it available in the target

Re: Json data extraction

rubenmarin — Sun, 03 Sep 2023 17:05:34 GMT

Hi, if it's loaded in Qlik you can use JsonGet() function: https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions/JsonGet.htm