It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor
product_json ##TODO## not null, check ( product_data is json ) ); create table orders ( order_id integer not null primary key, order_json ##TODO## not null, check ( order_data is json ) ); The tables are just a primary key, JSON column, & is json constraint
product_json ##TODO## not null, check ( product_data is json ) ); create table orders ( order_id integer not null primary key, order_json ##TODO## not null, check ( order_data is json ) ); But which data type to use for JSON?!
"bricks": [ { "colour": "red", "shape": "cube", "quantity": 13 }, { "colour": "green", "shape": "cube", "quantity": 17 }, … ] } We need to search for this value in the documents
json_table ( order_json columns ( customerId, nested products[*] columns ( productId, unitPrice ) ) ) t ) This tells the database to return a row for each element in the products array…
sysdate ); ORA-01861: literal does not match format string Remember the implicit conversions? It fails for dates! Use simple dot-notation to access the value
0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | |* 2 | DOMAIN INDEX | ORDERS_JSON_I | ----------------------------------------------------- With the search index in place, the optimizer can use it
FORMAT JSON , '$.orderDatetime' RETURNING TIMESTAMP NULL ON ERROR) >= TIMESTAMP' 2019-01-15 00:00:00') 2 - access("CTXSYS"."CONTAINS"("O"."ORDER_JSON", 'sdatap(TMS_orderDatetime >= "2019-01-15T00:00:00+00:00" /orderDatetime)')>0) Under the covers, this uses Oracle Text
returning date error on error null on empty ) ); It's more efficient to create a function- based index, matching the search you'll do This has some other benefits…
0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS | |* 2 | INDEX RANGE SCAN | ORDER_DATE_I | ------------------------------------------------------------ The function-based index is more efficient, so the optimizer will choose this over the search index
nested bricks[*] columns ( pos for ordinality, colour path '$.colour', shape path '$.shape', brick format json path '$' ) ) ) j Using JSON_table to extract the bricks as rows
bricks as ( select product_id, j.* from products, json_table ( … ) ) select … from bricks join costs on … We've joined the data, but how do we convert it back to JSON?
value b.quantity, 'unitCost' value c.cost ) from bricks b join costs c on b.colour = c.colour and b.shape = c.shape; So you can create a brick object with json_object…
'}' ) from bricks b join costs c on b.colour = c.colour and b.shape = c.shape; Add/replace this… …to this document … or use json_mergepatch (19c) to add it to the brick object
{ "colour": "green", "shape": "cuboid", "quantity": 17, "unitCost": 0.39 } This returns a row for each brick To combine them into an array for each product, use json_arrayagg
0.59 }, { "colour": "green", "shape": "cuboid", "quantity": 17, "unitCost": 0.39 }, … ] And replace this array in the product JSON with json_mergepatch
"bricks": [ { …, "unitCost": 0.59 }, { …, "unitCost": 0.39 }, … ] } Finally! We've added unitCost to every element in the array We just need to update the table…
NOT NULL BLOB ORDER_JSON$customerId NUMBER ORDER_JSON$orderDatetime VARCHAR2(32) ORDER_JSON$code VARCHAR2(8) ORDER_JSON$discountAmount NUMBER Sadly it only exposes scalar (non-array) values
cylinder blue 1 cylinder blue 1 cylinder green 1 cylinder green … … … The unique key for a brick is (colour, shape) Some products have duplicate entries in the bricks array! We're shipping too many bricks!
work with existing NoSQL deployments however, the more I believe that their schemaless nature has become an excuse for sloppiness and unwillingness to dwell on a project’s data model beforehand" - Florents Tselai https://tselai.com/modern-data-practice-and-the-sql-tradition.html
"PRODUCT_JSON$shape" shape, "PRODUCT_JSON$colour" colour, "PRODUCT_JSON$unitCost" unit_cost from product_bricks_vw ) select rownum brick_id, v.* from vals v; …and create a table from the results!
"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; 19c simplification (Storing the values as JSON if you want)