Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Choose Your Own JSON Adventure: NoSQL or SQL?

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.

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

April 30, 2021
Tweet

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