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:
- Simple query
- Using SQL/JSON query functions
- 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:
- 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.
