Part IV Query JSON Data
You can query JSON data using a simple dot notation or, for more functionality, using SQL/JSON functions and conditions. You can create and query a data guide that summarizes the structure and type information of a set of JSON documents.
To query particular JSON fields, or to map particular JSON fields to
SQL columns, you can use the SQL/JSON path language. In its simplest form a
path expression consists of one or more field names separated by periods
(.
). More complex path expressions can contain filters and
array indexes.
Oracle provides two ways of querying JSON content:
-
A dot-notation syntax, which is essentially a table alias, followed by a JSON column name, followed by one or more field names — all separated by periods (
.
). An array step can follow each of the field names. This syntax is designed to be simple to use and to return JSON values whenever possible. -
SQL/JSON functions and conditions, which completely support the path language and provide more power and flexibility than is available using the dot-notation syntax. You can use them to create, query, and operate on JSON data stored in Oracle Database.
-
Condition
json_exists
tests for the existence of a particular value within some JSON data. -
Conditions
is json
andis not json
test whether some data is well-formed JSON data. The former is used especially as a check constraint. -
Function
json_value
selects a scalar value from some JSON data, as a SQL value. -
Function
json_query
selects one or more values from some JSON data, as a SQL string representing the JSON values. It is used especially to retrieve fragments of a JSON document, typically a JSON object or array. -
Function
json_table
projects some JSON data as a virtual table, which you can also think of as an inline view.
-
Because the path language is part of the query language, no fixed schema is imposed on the data. This design supports schemaless development. A “schema”, in effect, gets defined on the fly at query time, by your specifying a given path. This is in contrast to the more usual approach with SQL of defining a schema (a set of table rows and columns) for the data at storage time.
Oracle SQL condition json_equal
does not accept a path-expression argument. It just compares two JSON values and
returns true if they are equal, false otherwise. For this comparison, insignificant
whitespace and insignificant object member order are ignored. For example, JSON
objects are equal if they have the same members, regardless of their order. However,
if either of two compared objects has one or more duplicate fields then the value
returned by json_equal
is unspecified.
You can generate and query a JSON data guide, to help you develop expressions for navigating your JSON content. A data guide can give you a deep understanding of the structure and type information of your JSON documents. Data guide information can be updated automatically, to keep track of new documents that you add.
See Also:
Oracle Database SQL Language Reference for complete information about the syntax and semantics of the SQL/JSON functions and conditions
- Simple Dot-Notation Access to JSON Data
Dot notation is designed for easy, general use and common use cases of querying JSON data. For simple queries it is a handy alternative to using SQL/JSON query functions. - SQL/JSON Path Expressions
Oracle Database provides SQL access to JSON data using SQL/JSON path expressions. - Clauses Used in SQL Functions and Conditions for JSON
ClausesRETURNING
, wrapper, error, and empty-field are described for SQL functions that use JSON data. Each clause is used in one or more of the SQL functions and conditionsjson_value
,json_query
,json_table
,json_serialize
,json_mergepatch
,is json
,is not json
,json_exists
, andjson_equal
. - SQL/JSON Condition JSON_EXISTS
SQL/JSON conditionjson_exists
lets you use a SQL/JSON path expression as a row filter, to select rows based on the content of JSON documents. You can use conditionjson_exists
in aCASE
expression or theWHERE
clause of aSELECT
statement. - SQL/JSON Function JSON_VALUE
SQL/JSON functionjson_value
selects JSON data and returns a SQL scalar or an instance of a user-defined SQL object type or SQL collection type (varray, nested table). - SQL/JSON Function JSON_QUERY
SQL/JSON functionjson_query
selects and returns one or more values from JSON data and returns those values. You can thus usejson_query
to retrieve fragments of a JSON document. - SQL/JSON Function JSON_TABLE
SQL/JSON functionjson_table
projects specific JSON data to columns of various SQL data types. You use it to map parts of a JSON document into the rows and columns of a new, virtual table, which you can also think of as an inline view. - Full-Text Search Queries
You can use Oracle SQL conditionjson_textcontains
in aCASE
expression or theWHERE
clause of aSELECT
statement to perform a full-text search of JSON data. You can use PL/SQL procedureCTX_QUERY.result_set
to perform facet search over JSON data. - JSON Data Guide
A JSON data guide lets you discover information about the structure and content of JSON documents stored in Oracle Database.