alter session set statistics_level = all

Storing JSON as BLOB

create table products (  
  product_id integer  
    generated by default as identity  
    not null   
    primary key,  
  product_json blob   
    not null  
    check ( product_json is json )  
)

create table orders ( 
  order_id integer 
    generated by default as identity 
    not null  
    primary key, 
  order_json blob 
    not null 
    check ( order_json is json ) 
)

create table costs (	 
  colour varchar2(30),  
  shape  varchar2(30),  
  cost   number 
)

Initialize the data

begin  
  
  insert into costs (colour,shape,cost) values ('green','cylinder',0.69);  
  insert into costs (colour,shape,cost) values ('blue','cylinder',0.98);  
  insert into costs (colour,shape,cost) values ('red','triangular prism',0.65);  
  insert into costs (colour,shape,cost) values ('green','triangular prism',0.98);  
  insert into costs (colour,shape,cost) values ('green','cuboid',0.28);  
  insert into costs (colour,shape,cost) values ('red','cube',0.67);  
  insert into costs (colour,shape,cost) values ('blue','cuboid',0.61);  
  insert into costs (colour,shape,cost) values ('red','cuboid',0.75);  
  insert into costs (colour,shape,cost) values ('green','cube',0.29);  
  insert into costs (colour,shape,cost) values ('blue','triangular prism',0.86);  
  insert into costs (colour,shape,cost) values ('blue','cube',0.35);  
  insert into costs (colour,shape,cost) values ('red','cylinder',0.39);  
  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (1,TO_CLOB(q'[{"productName":"ZILLANET","descripion":"Voluptate dolore anim magna ea ea in sit laborum cupidatat culpa non anim et nulla. Ex aliqua reprehenderit ipsum fugiat veniam dolore et commodo excepteur nostrud ex officia proident.","unitPrice":74.95,"bricks":[{"colour":"green","shape":"cylinder","quantity":11,"unitCost":0.69},{"colour":"blue","shape":"cylinder","quantity":20,"unitCost":0.98},{"colour":"green","shape":"cylinder","quantity":11,"unitCost":0.69},{"colour":"blue","shape":"cylinder","quanti]')  
  || TO_CLOB(q'[ty":20,"unitCost":0.98},{"colour":"red","shape":"triangular prism","quantity":12,"unitCost":0.65},{"colour":"green","shape":"cube","quantity":17,"unitCost":0.29},{"colour":"red","shape":"triangular prism","quantity":12,"unitCost":0.65},{"colour":"red","shape":"cube","quantity":10,"unitCost":0.67}]}]'));  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (2,TO_CLOB(q'[{"productName":"AVENETRO","descripion":"Anim ut et sunt quis excepteur officia culpa labore ea do elit in. Quis et consectetur amet quis minim sint esse consectetur ipsum magna occaecat cupidatat.","unitPrice":63.80,"bricks":[{"colour":"red","shape":"cube","quantity":16,"unitCost":0.67},{"colour":"red","shape":"cuboid","quantity":15,"unitCost":0.75},{"colour":"green","shape":"triangular prism","quantity":4,"unitCost":0.98},{"colour":"green","shape":"cube","quantity":8,"unitCost":0.29},{"colour":]')  
  || TO_CLOB(q'["blue","shape":"triangular prism","quantity":2,"unitCost":0.86}]}]'));  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (3,TO_CLOB(q'[{"productName":"SQUISH","descripion":"Excepteur id voluptate duis irure magna et pariatur. Cupidatat labore ipsum laboris occaecat cillum velit mollit et.","unitPrice":31.67,"bricks":[{"colour":"red","shape":"triangular prism","quantity":5,"unitCost":0.65},{"colour":"blue","shape":"cube","quantity":10,"unitCost":0.35},{"colour":"green","shape":"cuboid","quantity":10,"unitCost":0.28},{"colour":"blue","shape":"triangular prism","quantity":14,"unitCost":0.86},{"colour":"green","shape":"cylinder","q]')  
  || TO_CLOB(q'[uantity":8,"unitCost":0.69},{"colour":"blue","shape":"cube","quantity":10,"unitCost":0.35},{"colour":"green","shape":"cylinder","quantity":8,"unitCost":0.69}]}]'));  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (4,TO_CLOB(q'[{"productName":"APPLICA","descripion":"Labore magna culpa tempor do dolore eiusmod magna sunt. Laboris eu proident dolore consequat voluptate dolore ut voluptate laboris laboris elit tempor.","unitPrice":99.97,"bricks":[{"colour":"blue","shape":"triangular prism","quantity":17,"unitCost":0.86},{"colour":"red","shape":"cylinder","quantity":20,"unitCost":0.39},{"colour":"blue","shape":"cylinder","quantity":17,"unitCost":0.98},{"colour":"green","shape":"triangular prism","quantity":9,"unitCost":0.9]')  
  || TO_CLOB(q'[8},{"colour":"blue","shape":"cuboid","quantity":25,"unitCost":0.61},{"colour":"blue","shape":"cuboid","quantity":25,"unitCost":0.61}]}]'));  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (5,TO_CLOB(q'[{"productName":"MOMENTIA","descripion":"Mollit labore aliquip cupidatat eiusmod excepteur consectetur dolore ullamco sunt adipisicing anim aliqua deserunt. Proident voluptate adipisicing est commodo quis proident aute duis.","unitPrice":65.29,"bricks":[{"colour":"blue","shape":"cuboid","quantity":11,"unitCost":0.61},{"colour":"blue","shape":"cube","quantity":13,"unitCost":0.35},{"colour":"green","shape":"cuboid","quantity":10,"unitCost":0.28},{"colour":"red","shape":"cube","quantity":10,"unitCos]')  
  || TO_CLOB(q'[t":0.67}]}]'));  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (6,'{"productName":"DATAGENE","descripion":"Minim minim qui excepteur officia sunt laborum. Dolor consectetur Lorem anim et voluptate.","unitPrice":24.95,"bricks":[{"colour":"green","shape":"cuboid","quantity":16,"unitCost":0.28},{"colour":"green","shape":"cuboid","quantity":16,"unitCost":0.28},{"colour":"blue","shape":"cylinder","quantity":15,"unitCost":0.98},{"colour":"blue","shape":"triangular prism","quantity":10,"unitCost":0.86}]}');  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (7,'{"productName":"MEDIFAX","descripion":"Voluptate occaecat aute voluptate ut eu culpa aute. Velit sint consequat do tempor commodo elit.","unitPrice":20.99,"bricks":[{"colour":"red","shape":"triangular prism","quantity":11,"unitCost":0.65},{"colour":"green","shape":"cube","quantity":5,"unitCost":0.29},{"colour":"red","shape":"cube","quantity":5,"unitCost":0.67}]}');  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (8,'{"productName":"INTERFIND","descripion":"Excepteur minim veniam ut sint Lorem nulla aute laborum pariatur labore veniam. Duis excepteur esse culpa et non proident et dolor cupidatat Lorem laboris consectetur.","unitPrice":9.99,"bricks":[{"colour":"blue","shape":"cuboid","quantity":7,"unitCost":0.61},{"colour":"blue","shape":"cylinder","quantity":4,"unitCost":0.98},{"colour":"red","shape":"triangular prism","quantity":5,"unitCost":0.65}]}');  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (9,TO_CLOB(q'[{"productName":"OPPORTECH","descripion":"Ea esse non excepteur laboris culpa adipisicing esse labore excepteur. Mollit et nulla non labore consequat cupidatat nulla enim cupidatat ullamco cillum dolor ad ipsum.","unitPrice":52.62,"bricks":[{"colour":"green","shape":"triangular prism","quantity":15,"unitCost":0.98},{"colour":"green","shape":"cylinder","quantity":17,"unitCost":0.69},{"colour":"red","shape":"cuboid","quantity":5,"unitCost":0.75},{"colour":"blue","shape":"cube","quantity":15,"unitCo]')  
  || TO_CLOB(q'[st":0.35},{"colour":"blue","shape":"triangular prism","quantity":5,"unitCost":0.86},{"colour":"green","shape":"triangular prism","quantity":15,"unitCost":0.98},{"colour":"green","shape":"cuboid","quantity":5,"unitCost":0.28},{"colour":"blue","shape":"cube","quantity":15,"unitCost":0.35},{"colour":"blue","shape":"cylinder","quantity":3,"unitCost":0.98},{"colour":"red","shape":"cuboid","quantity":5,"unitCost":0.75}]}]'));  
  insert into products (PRODUCT_ID,PRODUCT_JSON) values (10,'{"productName":"GEEKWAGON","descripion":"Ut commodo in eu commodo aliqua duis occaecat fugiat ipsum in non. Reprehenderit pariatur qui ipsum ullamco duis sunt tempor consequat sint duis aliqua et nisi est.","unitPrice":35.97,"bricks":[{"colour":"red","shape":"cube","quantity":13,"unitCost":0.67},{"colour":"green","shape":"cuboid","quantity":7,"unitCost":0.28},{"colour":"blue","shape":"cylinder","quantity":10,"unitCost":0.98},{"colour":"red","shape":"cylinder","quantity":8,"unitCost":0.39}]}');  
    
  insert into orders  
  with ords as (  
    select level order_id ,  
           mod ( rownum, 23 ) + 1 customer_id,  
           date'2019-01-01' + ( rownum / 7 ) order_date  
    from   dual  
    connect by level <= 100  
  ), rws as (  
    select /*+ materialize no_merge */o.*, p.*,  
           mod ( round ( dbms_random.value ( 0, order_id ) ), 3 ) m1,  
           mod ( round ( dbms_random.value ( 0, product_id ) ), 4 )  m2  
    from   ords o  
    cross  join products p  
  )   
    select order_id,   
           json_object (  
             'customerId' value customer_id,   
             'orderDatetime' value to_char ( order_date, 'YYYY-MM-DD"T"HH24:MI:SS' ),  
             'products' value json_arrayagg (   
               json_object (  
                 'productId' value product_id ,  
                 'unitPrice' value r.product_json.unitPrice  
               )  
             )  
           )  
    from   rws r  
    where  m1 = m2  
    group  by order_id, customer_id, order_date  
    order  by 1;  
      
  commit;  
end; 

alter table orders 
  modify order_id  
  generated by default on null as identity ( 
      start with limit value  
  )

Querying JSON data

select * from products

JSON_serialize to convert data types

select json_serialize ( product_json returning clob pretty ) product_json  
from   products  
fetch first 2 rows only

select json_query ( product_json, '$' returning clob pretty )  
from   products  
fetch first 2 rows only

Simple-dot notation access

select * from products p  
where  p.product_json.unitPrice <= 20

JSON_value

select * from products p  
where  json_value (   
  product_json,   
  '$.unitPrice'   
    returning number   
) <= 20

Simple-dot notation data type methods

select * from products p  
where  p.product_json.unitPrice.number() <= 20

Returning an array with dot-notation

select o.order_json.products[*].productId   
from   orders o  
fetch first 5 rows only

select json_query (   
         order_json, '$.products[*].productId'  
           with array wrapper  
       )  
from   orders o  
fetch first 5 rows only

JSON_table

select order_id, t.*    
from   orders o, json_table (   
  order_json, '$'   
  columns (   
    customerId,   
    nested products[*] columns (   
      productId,   
      unitPrice   
    )    
  )   
) t  
order by order_id 
fetch first 10 rows only

Joining JSON_table output

with order_items as (  
  select order_id, t.*   
  from   orders o, json_table (  
    order_json, '$'  
    columns (  
      customerId,  
      nested products[*] columns (  
        productId,  
        unitPrice  
      )   
    )  
  ) t  
)  
select order_id,  
       p.product_json.productName product,  
       unitPrice  
from   order_items oi  
join   products p  
on     oi.productId = p.product_id  
where  customerId = 1  
order  by oi.order_id desc, p.product_id  
fetch first 10 rows only

Simple-dot notation implicit conversion

select * from orders o  
where  o.order_json.orderDatetime >= date '2019-01-15'

Date conversions

select * from orders o  
where  json_value (   
  order_json, '$.orderDatetime' returning date   
) >= date '2019-01-15'

Timestamp conversions

select * from orders o  
where  json_value (   
  order_json, '$.orderDatetime' returning timestamp   
) >= date '2019-01-15'

Dot notation date methods

select o.order_json.orderDatetime.date() order_date,  
       o.order_json.orderDatetime.timestamp() order_timestamp  
from   orders o  
where  o.order_json.orderDatetime.date() >= date '2019-01-15'

Indexing JSON

create index orders_date_i  
  on orders ( order_json )

JSON search index

create search index orders_json_i  
  on orders ( order_json )   
  for json  
  parameters ( 'sync (on commit)' )

exec dbms_stats.gather_table_stats ( null, 'orders' ) 

select * from orders o  
where  json_value (   
  order_json,   
  '$.orderDatetime'   
    returning timestamp   
) >= date '2019-01-15'

Query uses the JSON search index

select *      
from   table(dbms_xplan.display_cursor('a7zsavs72fv8w', 1, 'ALLSTATS LAST'))

Function-based indexes for JSON

create index order_date_i  
  on orders (   
    json_value (   
      order_json,   
      '$.orderDatetime' returning timestamp   
        error on error  
        null on empty  
    )  
  )

select * from orders o 
where  json_value (  
  order_json, '$.orderDatetime' returning timestamp  
) >= date '2019-01-15'

select *    
from   table(dbms_xplan.display_cursor('c9pm8wjpbhmfz', null, 'ALLSTATS LAST'))

Adding promotion details

insert into orders values (  
  default, '{  
  "customerId" : 9,  
  "orderDatetime" : "2019-01-15T06:51:26",  
  "products" :  
  [  
    {  
      "productId" : 4,  
      "unitPrice" : "31.67"  
    }  
  ],  
  "promotion": {  
    "code": "20OFF",  
    "discountAmount": 20  
  }  
}'  
)

commit

Adding unitCost to the bricks in each product

with bricks as (  
select product_id, j.*   
from   products,  
       json_table (  
         product_json 
         columns (  
           product format json path '$',  
           nested path bricks[*] 
           columns (   
             pos for ordinality,  
             colour,  
             shape,  
             quantity,  
             brick format json path '$'   
           )  
         )  
       ) j  
)  
  select json_object (   
           'colour'   value b.colour,   
           'shape'    value b.shape,   
           'quantity' value b.quantity,   
           'unitCost' value c.cost   
         )  
  from   bricks b  
  join   costs c  
  on     b.colour = c.colour  
  and    b.shape = c.shape  
  order  by product_id, b.colour, b.shape  
  fetch first 5 rows only

JSON_mergepatch

with bricks as (  
select product_id, j.*   
from   products,  
       json_table (  
         product_json  
         columns (  
           nested bricks[*]  
           columns (   
             pos      for ordinality,  
             colour,  
             shape,  
             brick format json path '$'   
           )  
         )  
       ) j  
)  
  select product_id,  
         b.colour,  
         b.shape,   
         cost,  
         json_mergepatch (   
           brick, '{ "unitCost": ' || to_char ( cost, '0.00' ) || '}'  
         )  
  from   bricks b  
  join   costs c  
  on     b.colour = c.colour  
  and    b.shape = c.shape  
  order  by product_id, b.colour, b.shape  
  fetch first 5 rows only

Re-creating the product JSON

with bricks as (  
select product_id, j.*   
from   products,  
       json_table (  
         product_json 
         columns (  
           product format json path '$',  
           nested path bricks[*] 
           columns (   
             pos      for ordinality,  
             colour,  
             shape,  
             quantity,  
             brick format json path '$'   
           )  
         )  
       ) j  
)  
  select product_id,  
         json_mergepatch (   
           product,  
           json_object (  
             'bricks' value json_arrayagg (   
               json_mergepatch (   
                 brick, '{ "unitCost": ' || to_char ( cost, '0.00' ) || '}'  
               )   
               order by pos  
             )  
           )  
         )  
  from   bricks b  
  join   costs c  
  on     b.colour = c.colour  
  and    b.shape = c.shape  
  group  by product_id, product  
  order  by product_id, product  
  fetch first 2 rows only

Update product JSON

update products p  
set    product_json = (  
with bricks as (  
select product_id, j.*   
from   products s,  
       json_table (  
         product_json 
         columns (  
           product format json path '$',  
           nested path bricks[*] 
           columns (   
             pos      for ordinality,  
             colour,  
             shape,  
             brick format json path '$'   
           )  
         )  
       ) j  
)  
  select json_mergepatch (   
           product,  
           json_object (  
             'bricks' value json_arrayagg (   
               json_mergepatch (   
                 brick, '{ "unitCost": ' || to_char ( cost, '0.00' ) || '}'  
               )   
               order by pos  
             )  
           )  
         )  
  from   bricks b  
  join   costs c  
  on     b.colour = c.colour  
  and    b.shape = c.shape  
  where  b.product_id = p.product_id  
  group  by product_id, product  
)

exec dbms_json.add_virtual_columns ( 'orders', 'order_json' )

JSON data guide columns

select * from orders 
fetch first 2 rows only

JSON Data Guide needs a search index

begin  
  dbms_json.add_virtual_columns (   
    'products', 'product_json'  
  );  
end; 

create search index products_json_i  
  on products ( product_json ) 
  for json

JSON data guide views

begin  
  dbms_json.create_view_on_path (   
    'product_bricks_vw', 'products', 'product_json', '$'  
  );   
end; 

begin 
  dbms_json.create_view_on_path (  
    'order_products_vw', 'orders', 'order_json', '$' 
  );  
end; 

select product_id,   
       "PRODUCT_JSON$shape" shape,   
       "PRODUCT_JSON$colour" colour  
from   product_bricks_vw  
order  by product_id, shape, colour  
fetch  first 8 rows only

Extract the bricks array into another table

create table bricks ( 
  brick_id primary key, 
  brick_json check ( brick_json is json ) 
) as   
  with vals as (   
    select distinct "PRODUCT_JSON$shape" "shape",  
           "PRODUCT_JSON$colour" "colour",  
           "PRODUCT_JSON$unitCost" "unitCost" 
    from   product_bricks_vw  
  )  
    select rownum brick_id,   
           json_object ( v.* ) brick_json  
    from   vals v

Create the junction table

create table product_bricks ( 
  product_id, brick_id, quantity, 
  primary key ( product_id, brick_id ) 
) as 
  select distinct product_id, brick_id, "PRODUCT_JSON$quantity" 
  from   bricks b 
  join   product_bricks_vw 
  on     "PRODUCT_JSON$shape" = b.brick_json.shape 
  and    "PRODUCT_JSON$colour" = b.brick_json.colour

Remove the bricks array

update products 
set    product_json = json_mergepatch (  
  product_json,  
  '{ "bricks": null }' 
)

select json_serialize (  
          product_json  
          returning varchar2(1000)  
          pretty  
       )  
from   products