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.

thatjeffsmith

April 30, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  2. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  3. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  4. Copyright © 2021, Oracle and/or its affiliates
    …both choices landed in an Oracle Database, with the full power of SQL?!?

    View full-size slide

  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
    }
    ]
    }

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  9. Copyright © 2021, Oracle and/or its affiliates
    You have some {json}, and you want to put it into a database.

    View full-size slide

  10. Copyright © 2021, Oracle and/or its affiliates
    [
    {},
    {},
    {},

    ]
    Array of JSON records

    View full-size slide

  11. Copyright © 2021, Oracle and/or its affiliates
    Database Actions and the SQL Worksheet
    Supports:
    •Excel
    •Delimited
    •AVRO
    •JSON
    •XML

    View full-size slide

  12. Copyright © 2021, Oracle and/or its affiliates
    “Flat” JSON ~ TABLE

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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;

    View full-size slide

  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;

    View full-size slide

  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;

    View full-size slide

  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

    View full-size slide

  19. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  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)

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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"));

    View full-size slide

  24. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  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

    View full-size slide

  26. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  27. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  28. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  29. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  30. Copyright © 2021, Oracle and/or its affiliates
    Query By Example (QBE) or Filtering your Documents

    View full-size slide

  31. Copyright © 2021, Oracle and/or its affiliates
    JSON Filtered Search (QBE) – get the equivalent SQL!
    Coming Soon!
    Summer 2021

    View full-size slide

  32. Copyright © 2021, Oracle and/or its affiliates
    Coming Soon!
    Summer 2021

    View full-size slide

  33. Copyright © 2021, Oracle and/or its affiliates
    🥤

    View full-size slide

  34. Copyright © 2021, Oracle and/or its affiliates

    View full-size slide

  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

    View full-size slide