Extracting data from a JSON column in the load script

[morenoju]

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

[francescoreggia · Accepted solution]

Try

Load

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

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

..

From ...

[morenoju]

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]

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]

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 

[Zafar]

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