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.
Because JSON data is stored in the database using standard data types (VARCHAR2, BLOB, and CLOB), SQL queries work with JSON data the same as with any other database data.
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 and is 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.
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.
Chapters
See Also:
Oracle Database SQL Language Reference for complete information about the syntax and semantics of the SQL/JSON functions
RETURNING, wrapper, error, and empty-field are described. Each is used in one or more of the SQL/JSON functions and conditions json_value, json_query, json_table, is json, is not json, and json_exists.json_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 condition json_exists in a CASE expression or the WHERE clause of a SELECT statement.json_value selects a scalar value from JSON data and returns it as a SQL value.json_query selects one or more values from JSON data and returns a string (VARCHAR2) that represents the JSON values. (Unlike function json_value, the return data type cannot be NUMBER). You can thus use json_query to retrieve fragments of a JSON document.json_table projects specific JSON data into VARCHAR2, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and SDO_GEOMETRY columns. You use it to decompose the result of JSON expression evaluation into the rows and columns of a new, virtual table, which you can also think of as an inline view.