Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
morenoju
Partner - Specialist
Partner - Specialist

Extracting data from a JSON column in the load script

Hi all,

In the Vertica DB I'm using in my Qlik Sense app, one of the tables called operation has a column that it's actually a JSON. Cells look like this:

{"id":{"type":4,"numeric":8798},"userName":{"type":1,"dbValue":"sysadmin"},"sessionDuration":{"type":4,"numeric":139}}

I'd need to extract the userName dbValue and the sessionDuration numeric and store them in a table, along with other columns from the operation table.

I'm thinking of treating the JSON column as string and just parse it using string functions. But to learn to do things cleaner, I wanted to first ask here in the community what's the best way to deal with JSON cells according to you.

Thanks much,

Juan

1 Solution

Accepted Solutions
francescoreggia
Partner - Contributor III
Partner - Contributor III

Try

Load

Trim(TextBeetwen([JSON FIELD]),'"userName":{"type":1,"dbValue":"','"')) as dbValue,

Trim(TextBeetwen([JSON FIELD]),'"sessionDuration":{"type":4,"numeric":"','}')) as sessionDuration,

..

From ...


View solution in original post

4 Replies
francescoreggia
Partner - Contributor III
Partner - Contributor III

Try

Load

Trim(TextBeetwen([JSON FIELD]),'"userName":{"type":1,"dbValue":"','"')) as dbValue,

Trim(TextBeetwen([JSON FIELD]),'"sessionDuration":{"type":4,"numeric":"','}')) as sessionDuration,

..

From ...


morenoju
Partner - Specialist
Partner - Specialist
Author

Thanks, Francesco. Complicating things further is probably not worthy. Corrected a couple of typos, and this worked fine for me:

Trim(TextBetween ((prop_list),'"userName":{"type":1,"dbValue":"','"')) as userName,

Trim(TextBetween((prop_list),'"sessionDuration":{"type":4,"numeric":','}')) as sessionDuration;

PuriVelasco
Creator
Creator

I have used our solution and it is good for me but I have a JSON file like this:

[{"begin":"25/06/2019 8:14:44","end":"25/06/2019 10:45:31"},{"begin":"25/06/2019 14:47:15","end":"25/06/2019 15:22:10"}]

I can access to the second "begin" because that field starts with     ,{

but I don't know how to access to the second "end".

Any ideas?

Thank you very much.

Zafar
Contributor II
Contributor II

Parse Json SQL SERVER column in Qlik Sense Load Editor

Hello Community,

 

Can you please help me to parse a sql server json column in qlik sense load editor .

Below is sample data in column coming

{"oldValue":"2019-10-31T23:55:00","newValue":"2019-10-25T00:00:00"}

 

thanks in Advance