Transform JSON data from "CLOB"

Jonathan_Alm — Mon, 14 Nov 2016 13:26:25 GMT

Hi,

I have a column in a database that for performance reason stores the data in the following format:

{

"period" : "201509",

"child_civicno" : "202422330",

"child_firstname" : "John",

"child_lastname" : "Doe",

"eventunit_code" : "NOFTH",

"eventunit_name" : "Norreka",

"dep_name" : "F3",

"event_startdate" : "150608",

"event_enddate" : "",

"event_ratecategory" : "HEL",

"measurementdate" : "15-SEP-15",

"addresstype" : "PUBLIC_RECORD",

"receiver_id" : "1287",

"municipal_code" : "1287",

"municipal_name" : "Galaxy"

}

So every row in column DATA has a lot of data that I need to extract and put into seperate columns.

Does anyone have a solution to transform this into columns with the corresponding data.

Kind regards, Jonathan

Re: Transform JSON data from "CLOB"

Anonymous — Mon, 14 Nov 2016 14:18:10 GMT

Heres a start and use generic load as in the link provided:

The Generic Load

data:
Load 1 as Dummy,
subfield(DATA, '"' ,1) as MyColumn,
subfield(DATA, '"' ,3) as MyData
;
Load * INLINE [
DATA,
"period" : "201509",
"child_civicno" : "202422330",
"child_firstname" : "John",
"child_lastname" : "Doe",
"eventunit_code" : "NOFTH",
"eventunit_name" : "Norreka",
"dep_name" : "F3",
"event_startdate" : "150608",
"event_enddate" : "",
"event_ratecategory" : "HEL",
"measurementdate" : "15-SEP-15",
"addresstype" : "PUBLIC_RECORD",
"receiver_id" : "1287",
"municipal_code" : "1287",
"municipal_name" : "Galaxy"
];

topic Transform JSON data from "CLOB" in QlikView

Transform JSON data from "CLOB"

Re: Transform JSON data from "CLOB"