Choose Your Own JSON Adventure: NoSQL or SQL?

The Oracle Database also offers a myriad of choices when working with JSON structured data! You can store the JSON via …

+ relational tables, and VARCHAR2, CLOB, or BLOB columns

+ relational tables, 12c or higher, storing them as VARCHAR2, CLOB, BLOB with an IS_JSON CHECK constraint – gives you access to a ton of built-in database JSON functions and features

+ using the database as a JSON Document Store and the accompanying SODA APIs to work with your JSON documents

+ 21c ups the ante and offers a native JSON data type for improved performance and features

These slides show the power of JSON unleashed with SQL will full ACID capabilities, or using our NoSQL interfaces to work with your data...but also with the scale-out performance and consistency of the Oracle Database.

You can also user our new hands-on LiveLab showcasing this technology in your own free Oracle Database.

Transcript

1. Copyright © 2021, Oracle and/or its affiliates
2. Copyright © 2021, Oracle and/or its affiliates
3. Copyright © 2021, Oracle and/or its affiliates
4. Copyright © 2021, Oracle and/or its affiliates …both choices landed in an Oracle Database, with the full power of SQL?!?
5. 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 } ] }
6. 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
7. 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
8. 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
9. Copyright © 2021, Oracle and/or its affiliates You have some {json}, and you want to put it into a database.
10. Copyright © 2021, Oracle and/or its affiliates [ {}, {}, {}, … ] Array of JSON records
11. Copyright © 2021, Oracle and/or its affiliates Database Actions and the SQL Worksheet Supports: •Excel •Delimited •AVRO •JSON •XML
12. Copyright © 2021, Oracle and/or its affiliates “Flat” JSON ~ TABLE
13. Copyright © 2021, Oracle and/or its affiliates Array of JSON records with Objects and Nested Arrays JSON Content stored as: •VARCHAR2 •CLOB •BLOB: 12c-19c preferred •JSON: 21c perf + features
14. Copyright © 2021, Oracle and/or its affiliates TABLE: AIRPORT_DELAYS • Each JSON Array item is stored as a new row • In each array, the JSON Objects have been stored as new JSON Columns
15. 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;
16. 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;
17. 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;
18. 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
19. Copyright © 2021, Oracle and/or its affiliates
20. 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)
21. 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
22. 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
23. 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"));
24. Copyright © 2021, Oracle and/or its affiliates
25. 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
26. Copyright © 2021, Oracle and/or its affiliates
27. Copyright © 2021, Oracle and/or its affiliates
28. Copyright © 2021, Oracle and/or its affiliates
29. Copyright © 2021, Oracle and/or its affiliates
30. Copyright © 2021, Oracle and/or its affiliates Query By Example (QBE) or Filtering your Documents
31. Copyright © 2021, Oracle and/or its affiliates JSON Filtered Search (QBE) – get the equivalent SQL! Coming Soon! Summer 2021
32. Copyright © 2021, Oracle and/or its affiliates Coming Soon! Summer 2021
33. Copyright © 2021, Oracle and/or its affiliates 🥤
34. Copyright © 2021, Oracle and/or its affiliates
35. Copyright © 2021, Oracle and/or its affiliates • Jeff’s {JSON} Blog Posts • Beda’s Blog Posts • SODA Product Technology Page • LiveSQL SODA for PL/SQL Tutorial Additional Resources