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

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

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:
Simple SQL
SQL/JSON query functions: JSON_VALUE, JSON_QUERY, JSON_TABLE
Dot-notation access to JSON data
This article describes these approaches with runtime examples.
References
Overview of SQL/JSON Path Expressions 
OakTable.net on JSON 
Database JSON Developer’s Guide
		DETERMINISTIC Functions in Oracle
		Find Which Objects are INVALID in Oracle – and Why
		How to Keep the Response Body with Non-2xx Responses in UTL_HTTP
		Running Procedures Asynchronously with Oracle Job Scheduler
Oracle Forums
Ask Tom
Oracle Forums
OraFAQ
Blogroll
Alberto Dell'Era
Amis Technology Blog
Data Warehousing with Oracle
JAVA, SQL AND JOOQ.
Jonathan Lewis
oracle-developer.net
SQL Maria
Sydney Oracle Lab
ThatJeffSmith
The Arup Nanda Blog