Skip to main content

JSON in Oracle Database: resources to get you started



I am (finally) diving in to explore and then write about using JSON in Oracle Database 12c - with SQL (Releases 1 and 2) and - more to the point for me - with PL/SQL (based on enhancements in Release 2).

Expect to see posts on this blog, articles in Oracle Magazine, quizzes and workouts at the Oracle Dev Gym, and more, in 2018.

To that end, I have been collecting links to useful resources on this topic, and I realized that you might benefit from that list just as much as I will. So why not share it now? :-)

Before exploring Oracle-specific resources, visit json.org to learn more about JSON itself.

Oracle Documentation

Let's start with the official source of truth: our doc set.

First and foremost, we have the Database JSON Developer's Guide, which covers how to store, generate, view, manipulate, manage, search, and query JSON.

And here are some specific links you might find handy:

Basic SQL/JSON Path Expression Syntax

The basic syntax of a SQL/JSON path expression is presented. It is composed of a context item followed by zero or more object or array steps, depending on the nature of the context item, followed optionally by a function step. Examples are provided.

JSON Data Guide

Lets you discover information about the structure and content of JSON documents stored in Oracle Database. Easily one of the coolest "added value" features of our implementation of JSON support.

Using PL/SQL with JSON Data

Explore the new types added in PL/SQL for JSON manipulation, and learn about how best to leverage SQL JSON features in PL/SQL.

Oracle Product Development

1. LiveSQL Tutorial by Mark Drake, Principal Product Manager

Make is the PM for XMLDB and for JSON in the database. He has put together a truly fantastic tutorial on LiveSQL that steps you through all the latest features for both SQL and PL/SQL in the 12.2 release. Here are the modules in the tutorial:


This is undoubtedly your best starting point for getting up to speed on JSON in Oracle Database.

Note: you will need to have an Oracle account to access the tutorial.

2. Top 10 Tips for JSON Processing in Database by Beda Hammerschmidt, Consulting Member of Technical Staff

Beda is the primary developer for JSON features in Oracle Database. So his command of the features is top-notch, and his understanding of JSON goes deep.

This video is long, but well worth the time for anyone wanting to get started right with this feature set.

3. Beda's blog on JSON, where as he puts it "I explain things in small steps." Perfect! :-)

Tim Hall / ORACLE-BASE

Tim Hall is no slouch when it comes to keeping up with the latest in Oracle Database - and offering clear, easy-to-follow articles on new features. JSON is no exception. The following two articles should give you a solid jumpstart on deploying JSON columns and related functionality in your applications.

JSON Support in Oracle Database 12c Release 1

SQL/JSON Functions in Oracle Database 12c Release 2

PL/SQL Object Types for JSON in Oracle Database 12c Release 2

Darryl Hurley / Implestrat

Darryl's been working with Oracle Database for a couple of decades, with a big focus on SQL and PL/SQL. He's helped me with several of my books, and has also written quizzes for Oracle Dev Gym.  After encountering resistance by UI developers in his organization to using Oracle Database for JSON-based development, he decided to get up to speed on this functionality - and share what he's learned with the community.

We recently held a CodeTalk session on this topic. Check out the video of our conversation and his demonstrations here.

Darryl is also in the midst of publishing a series of posts re: JSON on his blog.

And There's More

Here are a number of other links to blog posts, videos, code samples, and more on JSON in Oracle Database.

Github Examples by Gerald Venzl, Senior Principal Product Manager, Oracle Corporation

JSON in Motion, an Oracle Magazine article on 12.1 JSON by the ever-informative and -entertaining Arup Nanda, Oracle ACE Director

Run with JSON, an Oracle Magazine article on 12.2 JSON by Arup Nanda, Oracle ACE Director

JSON in Oracle Database with Examples, a blog post by Nimish Garg, Oracle ACE Associate

Well, you won't have learned a darned thing about JSON in Oracle Database simply by reading this blog post. But hopefully it can serve as a launch point for your exploration and education on this topic.

And if you've found some other outstanding content that you think I should add to this post, please let me know!
Labels:

Comments

  1. marceloacarrascoNovember 16, 2018 at 10:31 AM

    Hi Steven,

    I've been playing arround with json in Oracle 12.2, but I get an ORA-06512 error when I try to execute the following block...

    CREATE SEARCH INDEX IDX_JSON_RAW ON JSON_RAW(DOCUMENTO) FOR JSON;
    / -- worked ok

    DECLARE
    V_CLOB CLOB;
    BEGIN
    V_CLOB := DBMS_JSON.GET_INDEX_DATAGUIDE(tablename => 'JSON_RAW',
    jcolname => 'DOCUMENTO',
    format => DBMS_JSON.FORMAT_HIERARCHICAL,
    pretty => DBMS_JSON.PRETTY);
    DBMS_OUTPUT.PUT_LINE(V_CLOB);
    DBMS_JSON.CREATE_VIEW(viewname => 'DOCUMENTOS',
    tablename => 'JSON_RAW',
    jcolname => 'DOCUMENTO',
    dataguide => V_CLOB);
    END;
    /

    Error report -
    ORA-00904: : invalid identifier
    ORA-06512: at "XDB.DBMS_JSON", line 559
    ORA-06512: at "XDB.DBMS_JSON", line 977
    ORA-06512: at line 9
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:

    Any ideas why am I getting this error????

    Thanks in advance
    Marcelo

    ReplyDelete
    Replies
    1. Steven FeuersteinNovember 16, 2018 at 12:59 PM

      Please provide the DDL for the table json_raw.

      Delete
    2. Steven FeuersteinNovember 16, 2018 at 1:06 PM

      When I run the following statements, I get "ORA-40594: invalid data guide" (still sorting that out), but not invalid identifier.

      CREATE TABLE json_raw
      (
      documento CLOB CONSTRAINT ensure_json CHECK (documento IS JSON));
      /

      CREATE SEARCH INDEX IDX_JSON_RAW ON JSON_RAW(DOCUMENTO) FOR JSON
      /

      DECLARE
      v_clob CLOB;
      BEGIN
      v_clob :=
      dbms_json.get_index_dataguide (
      tablename => 'JSON_RAW',
      jcolname => 'DOCUMENTO',
      format => dbms_json.format_hierarchical,
      pretty => dbms_json.pretty);
      DBMS_OUTPUT.put_line (v_clob);
      dbms_json.create_view (viewname => 'DOCUMENTOS',
      tablename => 'JSON_RAW',
      jcolname => 'DOCUMENTO',
      dataguide => v_clob);
      END;
      /

      Delete
  2. ReinhardFebruary 28, 2019 at 9:55 AM

    Hi Steven,

    We process a JSON data structure coming from a rest interface (ORDS) and use JSON_OBJECT in order to validate the payload:

    l_json_obj := JSON_OBJECT_T.parse(json_entity);
    l_key_list := l_json_obj.get_keys;

    We need to transform the "key-values" into a pl/sql collection row. What is the best approach to to this? I want to avoid using SQL PIVOT and complex if-else. It would be nice to get an advice.

    Thanks in advance
    Reinhard

    ReplyDelete
  3. Steven FeuersteinFebruary 28, 2019 at 1:33 PM

    Reinhard,

    Can't you iterate through the array of keys, get the value for that key and populate the array? Does this script help?

    https://livesql.oracle.com/apex/livesql/file/content_GEZHJ12HQW7ACN66WST0HAKYN.htmlZ

    (sorry, I may be missing your challenge here)

    ReplyDelete
  4. Christian LeighJuly 17, 2019 at 3:47 AM

    Hi Steven,

    I have recently completed a project to migrate Tabular data from a DB2 platform into an Oracle Database that will store the vast bulk to its data in JSON format. To help my organisation achieve that migration I have built some tools (All Oracle PL/SQL based tools) that I have convinced my company to allow me to Open Source. I hope you won't think it too cheeky if I mention that these tools are available for free under the Apache 2 license from FINOS : https://github.com/finos/exodus

    ReplyDelete
  5. StevenFeuersteinJuly 17, 2019 at 3:50 AM

    This is fantastic, Christian. Thanks for providing this to the community. Do you have a Twitter account you can use to announce this?

    ReplyDelete
    Replies
    1. Christian LeighJuly 17, 2019 at 3:56 AM

      I don't really use Twitter, but I did announce this on UK Oracle User Group (UKOUG) through Linked-In.

      The announcement:-

      Announcing the release of EXODUS JSON Open Source Migration Tools for Oracle PL/SQL Developers.

      Exodus is a mapping, documenting, and migration tool that has been especially designed with Oracle PL/SQL developers in mind, with particular focus on transporting tabular data into a JSON centric storage paradigm. If you are comfortable with Oracle PL/SQL then all your mapping and migration code can be constructed here without the need for non-Oracle external tools. There is no magic “black box” behaviour. All of the auto-generated code (also in PL/SQL) is made available and viewable from within the tool, and because it’s in a language that seasoned Oracle developers are expert in, there will be no unexpected surprises with the technical approach.

      Delete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more