Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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;

Highlighted
Contributor III
Contributor III

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.

Highlighted
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