Choose Your Own JSON Adventure: NoSQL or SQL?

by thatjeffsmith

Slide 1

Slide 1 text

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Copyright © 2021, Oracle and/or its affiliates Easily consumed by applications • Maps to application objects • Nested structures • Read/write without joins Schema-flexible • Applications can evolve to store new attributes without modifying data definitions. Good exchange format • Supported by most programming languages • Human readable JSON Benefits { "name" : "Thomas Anderson", "job" : "Programmer", "addresses" : [ { "street" : "123 Main", "city" : "Santa Cruz", "zip" : 95041 } ] }

Slide 6

Slide 6 text

Copyright © 2021, Oracle and/or its affiliates Much more than a simple NoSQL document store Oracle Database & JSON SQL REST ✓ Full ISO SQL 2016 support over JSON ✓ Real-time analytics, joins, and aggregation ✓ ACID transactions ✓ Read-committed consistency ✓ Advanced security ✓ REST Data Services ✓ APEX low-code development ✓ In-database full-text search

Slide 7

Slide 7 text

Copyright © 2021, Oracle and/or its affiliates Two Contrasting Database Architecture Strategies Run a Converged database that supports multiple data types and workloads Run Single-Purpose, "best-of-breed" database for each data type and workload Spatial ML Blockchain Document Graph Reporting

Slide 8

Slide 8 text

Copyright © 2021, Oracle and/or its affiliates Oracle makes it simple to use JSON and XML documents Freely mix JSON and non-JSON data types in Relational Tables Native Data Guide allows you to quickly determine what data you have Transparent scale-out with Full ACID transactions Index any JSON element for fast OLTP Declarative Parallel SQL analytics on all formats Run complex joins across multiple JSON documents and collections No need for custom application code to accomplish basic data management tasks Create Value Using Document Data

Slide 9

Slide 9 text

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Copyright © 2021, Oracle and/or its affiliates Using SQL on our new TABLE, simple DOT notation SELECT A.NAME, SUM(A.STATISTICS."Minutes Delayed".CARRIER) "Minutes Delayed" FROM AIRPORT_DELAYS A GROUP BY A.NAME ORDER BY 2 DESC;

Slide 16

Slide 16 text

Copyright © 2021, Oracle and/or its affiliates Using SQL on our TABLE, JSON_VALUE() select json_value ( Statistics, '$.Flights.Cancelled' returning number ) cancled_flights from airport_delays a where a.id = 5;

Slide 17

Slide 17 text

Copyright © 2021, Oracle and/or its affiliates Using SQL on our table, JSON_TABLE() select a.name, v.* from airport_delays a, json_table ( a.time, '$' columns ( Label, Year, Month, "Month Name" ) )v where a.id = 100;

Slide 18

Slide 18 text

Copyright © 2021, Oracle and/or its affiliates Using SQL on our table, nested arrays select t.* from airport_delays, json_table(Statistics, '$.Carriers."Aircraft Types"[*]' columns( make varchar2(400) path '$.make', nested path '$.models[0,1]' columns( models varchar2(400) path '$' ) ) ) as t where id = 100; 0 1 0 1

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Copyright © 2021, Oracle and/or its affiliates • Set of NoSQL-style APIs • Manage collections of JSON documents • Available interfaces: 1. Java 2. REST APIs (ORDS) 3. C 4. Node.js 5. Python 6. CLI (sqlcl) 7. Web (Database Actions)

Slide 21

Slide 21 text

Public 21 JSON Document Store SQL Simple collections create, read, update Fast and scalable JSON storage Real-time analytics directly over JSON Oracle Database SODA REST, Java, NodeJS, Python, PL/SQL, ODPI-C, OCI

Slide 22

Slide 22 text

Multiple Predicates with Ordering Distance Search Order By Exact Match List of Values Full Text Searching {"$query":{},"$orderby":{"releaseDate":-1}} {"location.city":"SAN FRANCISCO"} {"id":{"$in":[245168,299687,177572,76757]}} {"plot":{"$contains":”Joker"}} {"movieId":109410, "startTime":{ "$gte":"2016-09-12T07:00:00.000Z", "$lt":"2016-09-13T07:00:00.000Z“ }, "$orderby":{"screenId":1,"startTime":2} } {"plot":{"$contains":{"$fuzzy":”Joke"}}} {"location.geoCoding":{ "$near":{ "$geometry":{ "type":"Point", "coordinates":[37.8953,-122.1247] }, "$distance":5, "$unit":"mile" }}} SODA Query-By-Example / Filtering

Slide 23

Slide 23 text

Copyright © 2021, Oracle and/or its affiliates SODA & Java/JDBC • Facilities to read, write, and modify JSON type values from Java • JDBC package oracle.sql.json • Features • Mutable tree/object model • Stream-based parser and generator • Access to extended SQL/JSON types (TIMESTAMP, DATE, etc.) • Supports both JSON text and OSON • Integration with JSON-P / JSR-374 and JSON-B / JSR-367 ResultSet rs = stmt.executeQuery("SELECT data FROM emp"); rs.next(); OracleJsonObject emp = rs.getObject(1, OracleJsonObject.class); System.out.println(emp.get("name"));

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Copyright © 2021, Oracle and/or its affiliates Wait, what is SQLcl? • New & Improved SQL*Plus • Lightweight – 30mb download, no $ORACLE_HOME • Modern interface and commands • Access to entire SODA library • Available in OCI YUM Repo and OCI Cloud Shell