Querying JSON Data in Oracle: SQL/JSON Query Functions, Dot Notation

November 21, 2017

in JSON, SQL

Querying JSON in Oracle via JSON_QUERY, JSON_VALUE, JSON_TABLE, dot notation syntax

The last article talked about how to store JSON in the database. This article shows you how to retrieve it meaningfully using various query approaches in Oracle 12c.

For the demo, we’ll use the same old CUSTOMER table with JSON metadata.

The table:

-- Customer table DDL with JSON metadata
CREATE TABLE customer
  (custid    VARCHAR2(5) NOT NULL PRIMARY KEY,
   custname  VARCHAR2(50) NOT NULL,
   metadata  VARCHAR2(1000)
   CONSTRAINT chk_json CHECK (metadata IS JSON));

The data:

 CUSTID CUSTNAME METADATA
 ------ -------- --------------------------------------
 1      Susan    {"Mobile"       : "(555) 555-1234",
                  "OfficePh"     : "(651)-555-1999",
                  "HomePh"       : "(651) 399-9262",
                 }
 
 2      Martin   {"Mobile"       : "(555) 321-4311",
                  "HomePh"       : "(627)-444-2888",
                  "HomePhAlt"    : "(627) 587-6623"
                 }

We’ll look at three approaches for querying JSON data in Oracle 12c:

  1. Simple query
  2. Using SQL/JSON query functions
  3. Dot-notation access

1. Simple Query

The easiest way to retrieve JSON data from Oracle is via simple SQL, the kind you’d write even if there were no JSON in the picture.

-- 1. Simple query
select * from customer;

When run:

SQL> -- 1. Simple query
 SQL> select * from customer;
 
 CUSTID CUSTNAME METADATA
 ------ -------- --------------------------------------
 1      Susan    {"Mobile"       : "(555) 555-1234",
                     "OfficePh"     : "(651)-555-1999",
                     "HomePh"       : "(651) 399-9262",
                    }
 
 2      Martin   {"Mobile"       : "(555) 321-4311",
                     "HomePh"       : "(627)-444-2888",
                     "HomePhAlt"    : "(627) 587-6623"
                   }

You need nothing more than simple SQL if the JSON data has to be consumed as-is by the calling program. A little extra work has to be done if the JSON needs to be translated to relational form or filtered based on certain conditions. That’s where SQL/JSON query functions and dot notation come in.

2. SQL/JSON Query Functions: JSON_VALUE, JSON_QUERY, JSON_TABLE

Just as SQL/XML gives SQL access to XML data using XQuery expressions, SQL/JSON gives SQL access to JSON data using SQL/JSON path expressions.

2.1 JSON_VALUE

JSON_VALUE finds a specific scalar value in JSON data and returns it to SQL. Its full syntax is illustrated in Oracle’s SQL Language Reference; for our demo this will suffice:

json_value(<JSON column name>, ‘$.<JSON path>’)

-- 2.1a  SQL/JSON query: JSON_VALUE
select custid
     , custname
     , json_value(metadata, '$.HomePh') homeph
     , json_value(metadata, '$.OfficePh') officeph
from customer; 

When run:

SQL> -- 2.1a  SQL/JSON query: JSON_VALUE
SQL> select custid
  2       , custname
  3       , json_value(metadata, '$.HomePh') homeph
  4       , json_value(metadata, '$.OfficePh') officeph
  5  from customer;

CUSTID CUSTNAME HOMEPH           OFFICEPH
------ -------- ---------------- ----------------
1      Susan    (651) 399-9262   (651)-555-1999
2      Martin   (627)-444-2888

JSON_VALUE can optionally take a RETURNING clause to specify the returned value’s datatype and precision.

-- 2.1b  SQL/JSON query: JSON_VALUE with RETURNING
select custid
     , custname
     , json_value(metadata, '$.HomePh'
       returning varchar2(16)) homeph
     , json_value(metadata, '$.OfficePh'
       returning varchar2(16)) officeph
from customer; 

When run:

SQL> -- 2.1b  SQL/JSON query: JSON_VALUE with RETURNING
SQL> select custid
  2       , custname
  3       , json_value(metadata, '$.HomePh'
  4         returning varchar2(16)) homeph
  5       , json_value(metadata, '$.OfficePh'
  6         returning varchar2(16)) officeph
  7  from customer;

CUSTID CUSTNAME HOMEPH           OFFICEPH
------ -------- ---------------- ----------------
1      Susan    (651) 399-9262   (651)-555-1999
2      Martin   (627)-444-2888

Note that if we try to select data from a non-existent name-value pair in the JSON document, SQL does not treat it as an error — it simply returns no data.

2.2 JSON_QUERY

JSON_QUERY finds one or more matching values in JSON data and returns it to SQL as a string. JSON_QUERY can be used to retrieve fragments of a JSON document.

With ‘$’ as path expression, JSON_QUERY returns the entire JSON data for the row.

-- 2.2a  SQL/JSON query: JSON_QUERY
--       to select the entire JSON document
select custid
     , custname
     , json_query(metadata, '$' 
                  ) json_metadata
from customer; 

When run:

SQL> -- 2.2a  SQL/JSON query: JSON_QUERY
SQL> --       to select the entire JSON document
SQL> select custid
  2       , custname
  3       , json_query(metadata, '$'
  4                    ) json_metadata
  5  from customer;

CUSTID CUSTNAME JSON_METADATA
------ -------- ---------------------------
1      Susan    {"Mobile":"(555) 555-1234",
                "OfficePh":"(651)-555-1999"
                ,"HomePh":"(651) 399-9262"}

2      Martin   {"Mobile":"(555) 321-4311",
                "HomePh":"(627)-444-2888","
                HomePhAlt":"(627) 587-6623"
                }

If JSON_QUERY returns scalar values or multiple values, it needs a WITH WRAPPER clause — this encloses the output in an array wrapper (i.e. square brackets []).

-- 2.2b  SQL/JSON query: JSON_QUERY
--       with wrapper (scalar values)
select custid
     , custname
     , json_query(metadata, '$.HomePh' 
                  with wrapper) homeph
from customer; 
-- 2.2c  SQL/JSON query: JSON_QUERY
--       with wrapper (multiple values)
select custid
     , custname
     , json_query(metadata, '$.*' 
                  with wrapper) json_metadata
from customer; 

When run:

SQL> -- 2.2b  SQL/JSON query: JSON_QUERY
SQL> --       with wrapper (scalar values)
SQL> select custid
  2       , custname
  3       , json_query(metadata, '$.HomePh'
  4                    with wrapper) homeph
  5  from customer;

CUSTID CUSTNAME HOMEPH
------ -------- ------------------
1      Susan    ["(651) 399-9262"]
2      Martin   ["(627)-444-2888"]
SQL> -- 2.2c  SQL/JSON query: JSON_QUERY
SQL> --       with wrapper (multiple values)
SQL> select custid
  2       , custname
  3       , json_query(metadata, '$.*'
  4                    with wrapper) json_metadata
  5  from customer;

CUSTID CUSTNAME JSON_METADATA
------ -------- ---------------------------
1      Susan    ["(555) 555-1234","(651)-55
                5-1999","(651) 399-9262"]

2      Martin   ["(555) 321-4311","(627)-44
                4-2888","(627) 587-6623"]
 

The output may be pretty printed with the PRETTY clause.

-- 2.2d  SQL/JSON query: JSON_QUERY
--       with wrapper (pretty print)
select custid
     , custname
     , json_query(metadata, '$.*' 
          pretty with wrapper) json_metadata
from customer; 

When run:

SQL> -- 2.2d  SQL/JSON query: JSON_QUERY
SQL> --       with wrapper (pretty print)
SQL> select custid
  2       , custname
  3       , json_query(metadata, '$.*'
  4            pretty with wrapper) json_metadata
  5  from customer;

CUSTID CUSTNAME JSON_METADATA
------ -------- ---------------------------
1      Susan    [
                  "(555) 555-1234",
                  "(651)-555-1999",
                  "(651) 399-9262"
                ]

2      Martin   [
                  "(555) 321-4311",
                  "(627)-444-2888",
                  "(627) 587-6623"
                ]

See the 12.1 documentation for more JSON_QUERY examples.

2.3 JSON_TABLE

JSON_TABLE maps JSON data into relational rows and columns, as an inline view.

-- 2.3a  SQL/JSON query: JSON_TABLE
select custid
     , custname
     , homeph
     , officeph
from   customer
     , json_table
       (metadata,'$'
          columns (
            homeph   varchar2(20) path '$.HomePh'
          , officeph varchar2(20) path '$.OfficePh'
                  )
       );

When run:

SQL> -- 2.3a  SQL/JSON query: JSON_TABLE
SQL> select custid
  2       , custname
  3       , homeph
  4       , officeph
  5  from   customer
  6       , json_table
  7         (metadata,'$'
  8            columns (
  9              homeph   varchar2(20) path '$.HomePh'
 10            , officeph varchar2(20) path '$.OfficePh'
 11                    )
 12         );

CUSTID CUSTNAME HOMEPH             OFFICEPH
------ -------- ------------------ ----------------
1      Susan    (651) 399-9262     (651)-555-1999
2      Martin   (627)-444-2888

That above was a simple example of JSON_TABLE usage. Complex implementations are possible with JSON arrays, nested JSON paths, etc. — JSON_TABLE Options: Error Handling, Nested Path tells us more.

3. Dot-Notation Access to JSON

Dot notation is an easy way to query JSON data for basic use cases. The dot-notation query returns a string (VARCHAR2) representing JSON data.

If the dot-notation query matches a single JSON value, it returns the value in the string. If the dot-notation query matches multiple JSON values, it returns a JSON array containing the matched values.

  • If the single JSON value is a scalar value, the dot-notation works like JSON_VALUE (example 2.1a)
  • If the single JSON value is a JSON object, dot-notation works like JSON_QUERY for an object (example 2.2a)
-- 3.1 Dot-Notation query 
--     Matches single JSON value; returns the 
--     matched scalar value (similar to JSON_VALUE)
select custid
     , custname
     , c.metadata.HomePh homeph
     , c.metadata.OfficePh officeph
from   customer c;
-- 3.2 Dot-Notation query: 
--     Matches JSON object; returns the
--     matched object (similar to JSON_QUERY)
select custid
     , custname
     , c.metadata json_metadata
from   customer c;

When run:

SQL> -- 3.1 Dot-Notation query
SQL> --     Matches single JSON value; returns the
SQL> --     matched scalar value (similar to JSON_VALUE)
SQL> select custid
  2       , custname
  3       , c.metadata.HomePh homeph
  4       , c.metadata.OfficePh officeph
  5  from   customer c;

CUSTID CUSTNAME HOMEPH             OFFICEPH
------ -------- ------------------ ----------------
1      Susan    (651) 399-9262     (651)-555-1999
2      Martin   (627)-444-2888
SQL> -- 3.2 Dot-Notation query:
SQL> --     Matches JSON object; returns the
SQL> --     matched object (similar to JSON_QUERY)
SQL> select custid
  2       , custname
  3       , c.metadata json_metadata
  4  from   customer c;

CUSTID CUSTNAME JSON_METADATA
------ -------- --------------------------------------
1      Susan    {"Mobile"       : "(555) 555-1234",
                    "OfficePh"     : "(651)-555-1999",
                    "HomePh"       : "(651) 399-9262",
                   }

2      Martin   {"Mobile"       : "(555) 321-4311",
                    "HomePh"       : "(627)-444-2888",
                    "HomePhAlt"    : "(627) 587-6623"
                   }

Summary

JSON data in Oracle can be queried via:

  1. Simple SQL
  2. SQL/JSON query functions: JSON_VALUE, JSON_QUERY, JSON_TABLE
  3. Dot-notation access to JSON data

This article describes these approaches with runtime examples.

References

Leave a Comment

Previous post:

Next post: