Transform JSON data from "CLOB"

[Jonathan_Alm]

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

[Anonymous]

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"
];