Json data extraction

[suvbin]

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.

[OritA]

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

[sureshkumar]

Hello @suvbin 

Please refer below white paper documentation for json data extract

Regards,
Suresh

[Heinvandenheuvel]

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 

[john_wang]

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.

[suvbin]

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 

[rubenmarin]

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