Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Heres a start and use generic load as in the link provided:
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"
];