JSON to Table using ODI 12c

JSON to Table using ODI 12c:

Scripts

Source: [                 {                                 "id": "0001",                                 "type": "donut",                                 "name": "Cake",                                 "ppu": 0.55,                                 "batters":                                                 {                                                                 "batter":                                                                                 [                                                                                                 { "id": "1001", "type": "Regular" },                                                                                                 { "id": "1002", "type": "Chocolate" },                                                                                                 { "id": "1003", "type": "Blueberry" },                                                                                                 { "id": "1004", "type": "Devil's Food" }                                                                                 ]                                                 },                                 "topping":                                                 [                                                                 { "id": "5001", "type": "None" },                                                                 { "id": "5002", "type": "Glazed" },                                                                 { "id": "5005", "type": "Sugar" },                                                                 { "id": "5007", "type": "Powdered Sugar" },                                                                 { "id": "5006", "type": "Chocolate with Sprinkles" },                                                                 { "id": "5003", "type": "Chocolate" },                                                                 { "id": "5004", "type": "Maple" }                                                 ]                 },                 {                                 "id": "0002",                                 "type": "donut",                                 "name": "Raised",                                 "ppu": 0.55,                                 "batters":                                                 {                                                                 "batter":                                                                                 [                                                                                                 { "id": "1001", "type": "Regular" }                                                                                 ]                                                 },                                 "topping":                                                 [                                                                 { "id": "5001", "type": "None" },                                                                 { "id": "5002", "type": "Glazed" },                                                                 { "id": "5005", "type": "Sugar" },                                                                 { "id": "5003", "type": "Chocolate" },                                                                 { "id": "5004", "type": "Maple" }                                                 ]                 },                 {                                 "id": "0003",                                 "type": "donut",                                 "name": "Old Fashioned",                                 "ppu": 0.55,                                 "batters":                                                 {                                                                 "batter":                                                                                 [                                                                                                 { "id": "1001", "type": "Regular" },                                                                                                 { "id": "1002", "type": "Chocolate" }                                                                                 ]                                                 },                                 "topping":                                                 [                                                                 { "id": "5001", "type": "None" },                                                                 { "id": "5002", "type": "Glazed" },                                                                 { "id": "5003", "type": "Chocolate" },                                                                 { "id": "5004", "type": "Maple" }                                                 ]                 } ]

Target DDL:   CREATE TABLE "DEMO"."JSON_TABLE"    (           "FILE_NAME" VARCHAR2(255 BYTE),                 "LOAD_DATE" VARCHAR2(255 BYTE),                 "ID" NUMBER(10,0),                 "NAME" VARCHAR2(255 BYTE),                 "PPU" NUMBER(10,2),                 "TYPE" VARCHAR2(255 BYTE),                 "SEQ_NUM" NUMBER(10,0),                 "BATTER_ID" NUMBER(10,0),                 "BATTER_TYPE" VARCHAR2(255 BYTE),                 "TOPPING_ID" NUMBER(10,0),                 "SEQ_TOPPING" NUMBER(10,0),                 "TOPPING_TYPE" VARCHAR2(255 BYTE)    )

Topology Configuration:

Create new Data Server under "ComplexFile" Technology as mentioned above

Go to JDBC Tab, specify 

          Select JDBC Driver and

          JDBC URL as mentioned above

[Note: If you don't have XSD handy please click on Edit nXSD and proceed to generate XSD]

Provide the properties as mentioned above:

1. DTD : XSD file location we need to provide here
2. File : Actual file location we need to provide here
3. root_elt : root_element of your XSD
4. schema : Specify schema name or file name of the XSD

Create Data Server as mentioned below:

Create Physical Schema as mentioned below: 

Please specify your own schema name.

Create Logical Schema as mentioned below:

Go to Designer Navigator and 

Create Model as specified below:

Go to Selective Reverse-Engineering tab and select as mentioned below and click on Reverse Engineering

Reverse Engineer the target data store into corresponding model.

Go to Project Accordion under designer navigator

Create Project

Create Mapping as mentioned below:

Select Join and specify Execution on Hint as "Stage" for all the joins as mentioned below:

Run the mapping

Output: