Skip Headers
Oracle® XML DB Developer's Guide
12c Release 1 (12.1)

E41152-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

39 JSON in Oracle Database

This chapter describes how to use the JavaScript Object Notation (JSON) with Oracle Database. It covers native Oracle Database support for JSON, including querying and indexing.

The chapter contains these topics:

Overview of JSON

JavaScript Object Notation (JSON) is defined in standards ECMA-404 (JSON Data Interchange Format) and ECMA-262 (ECMAScript Language Specification, third edition). The JavaScript dialect of ECMAScript is a general programming language used widely in web browsers and web servers.

JSON is almost a subset of the object literal notation of JavaScript.Foot 1  Because it can be used to represent JavaScript object literals, JSON commonly serves as a data-interchange language. In this it has much in common with XML.

Because it is (almost a subset of) JavaScript notation, JSON can often be used in JavaScript programs without any need for parsing or serializing. It is a text-based way of representing JavaScript object literals, arrays, and scalar data.

Although it was defined in the context of JavaScript, JSON is in fact a language-independent data format. A variety of programming languages can parse and generate JSON data.

JSON is relatively easy for humans to read and write, and easy for software to parse and generate. It is often used for serializing structured data and exchanging it over a network, typically between a server and web applications.

See Also:

Overview of JSON Syntax and the Data It Represents

A JSON value is one of the following: object, array, number, string, true, false, or null. All values except objects and arrays are scalar.

Note:

A JSON value of null is a value as far as SQL is concerned. It is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data). In particular, SQL condition IS NULL condition returns false for a JSON null value, and SQL condition IS NOT NULL condition returns true.

A JavaScript object is an associative array, or dictionary, of zero or more pairs of property names and associated JSON values.Foot 2  A JSON object is a JavaScript object literal.Foot 3  It is written as such a property list enclosed in braces ({, }), with name-value pairs separated by commas (,), and with the name and value of each pair separated by a colon (:).

In JSON each property name and each string value must be enclosed in double quotation marks ("). In JavaScript notation, a property name used in an object literal can be, but need not be, enclosed in double quotation marks. It can also be enclosed in single quotation marks (').

As a result of this difference, in practice, data that is represented using unquoted or single-quoted property names is sometimes referred to loosely as being represented in JSON, and some implementations of JSON, including the Oracle Database implementation, support the lax syntax that allows the use of unquoted and single-quoted property names.

A string in JSON is composed of Unicode characters, with backslash (\) escaping. A JSON number (numeral) is represented in decimal notation, possibly signed and possibly including a decimal exponent.

An object property is sometimes called a key. An object property name-value pair is sometimes called an object member. Order is not significant among object members.

Note:

Each key in a given JSON object is not necessarily unique; the same key may be repeated. The JSON path evaluation that Oracle Database employs always uses only one of the object members that have a given key; any other members with the same key are ignored. It is unspecified which of multiple such members is used.

See also "Unique Versus Duplicate Keys in JSON Objects".

A JavaScript array has zero or more elements. In JSON, an array is represented by brackets ([, ]) surrounding the representations of the array elements, which are separated by commas (,), and each of which is an object, an array, or a scalar value. Array element order is significant.

Example 39-1 shows a JSON object that represents a purchase order, with top-level property names PONumber, Reference, Requestor, User, Costcenter, ShippingInstruction, Special Instructions, AllowPartialShipment and LineItems.

Example 39-1 A JSON Object (Representation of a JavaScript Object Literal)

{ "PONumber"             : 1600,
  "Reference"            : "ABULL-20140421",
  "Requestor"            : "Alexis Bull",
  "User"                 : "ABULL",
  "CostCenter"           : "A50",
  "ShippingInstructions" : { "name"   : "Alexis Bull",
                             "Address": { "street"  : "200 Sporting Green",
                                          "city"    : "South San Francisco",
                                          "state"   : "CA",
                                          "zipCode" : 99236,
                                          "country" : "United States of America" },
                             "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },
                                         { "type" : "Mobile", "number" : "415-555-1234" } ] },
  "Special Instructions" : null,
  "AllowPartialShipment" : false,
  "LineItems"            : [ { "ItemNumber" : 1,
                               "Part"       : { "Description" : "One Magic Christmas",
                                                "UnitPrice"   : 19.95,
                                                "UPCCode"     : 13131092899 },
                               "Quantity"   : 9.0 },
                             { "ItemNumber" : 2,
                               "Part"       : { "Description" : "Lethal Weapon",
                                                "UnitPrice"   : 19.95,
                                                "UPCCode"     : 85391628927 },
                               "Quantity"   : 5.0 } ] }
  • Most of the properties have string values. For example: User has value "ABULL".

  • Properties PONumber and zipCode have numeric values: 1600 and 99236.

  • Property Shipping Instructions has an object as value. This object has three members, with properties name, Address, and Phone. Property name has a string value ("Alexis Bull"). Properties Address and Phone each have an object value.

  • The value of property Address is an object with properties street, city, state, zipCode, and country. Property zipCode has a numeric value; the others have string values.

  • Property Phone has an array as value. This array has two elements, each of which represents an object literal. Each of these objects has two members: properties type and number and their values.

  • Property Special Instructions has a null value.

  • Property AllowPartialShipment has the Boolean value true.

  • Property LineItems has an array as value. This array has two elements, each of which represents an object literal. Each of these objects has three members, with keys ItemNumber, Part, and Quantity.

  • Properties ItemNumber and Quantity have numeric values. Property Part has an object as value, with properties Description, UnitPrice, and UPCCode. Property Description has a string value. Properties UnitPrice and UPCCode have numeric values.

Overview of JSON Compared with XML

Both JSON and XML (Extensible Markup Language) are commonly used as data-interchange languages. Here is a brief list of some of their differences.

  • JSON data types are few and predefined. XML data can be either typeless or based on an XML schema or a document type definition (DTD).

  • JSON has simple structure-defining and document-combining constructs: it lacks attributes, namespaces, inheritance and substitution.

  • The order of the members of a JavaScript object literal is insignificant. In general, order matters within an XML document.

  • JSON lacks an equivalent of XML text nodes (XPath node test text()). In particular, this means that there is no mixed content.

JSON is most useful with simple, structured data. XML is useful for both structured and semi-structured data. JSON is generally data-centric, not document-centric; XML can be either. JSONis not a markup language; it is designed only for data representation. XML is both a document markup language and a data representation language.

Because of its simple definition and features, JSON data is generally easier to generate, parse, and process than XML data. Use cases that involve combining different data sources generally lend themselves well to the use of XML, because it offers namespaces and other constructs facilitating modularity and inheritance.

JSON, unlike XML (and unlike JavaScript), has no date data type. A date is represented in JSON using the available data types, such as string. There are some de facto standards for converting between real dates and strings. But programs using JSON must, one way or another, deal with date representation conversion.

Overview of JSON in Oracle Database

JSON data and XML data can be used in Oracle Database in similar ways. Unlike relational data, both can be stored, indexed, and queried in the database without any need for a schema that defines the data.

JSON data has often been stored in NoSQL databases such as Oracle NoSQL Database and Oracle Berkeley DB. These allow for storage and retrieval of data that is not based on any schema, but they do not offer the rigorous consistency models of relational databases.

To compensate for this shortcoming, a relational database is sometimes used in parallel with a NoSQL database. Applications using JSON data stored in the NoSQL database must then ensure data integrity themselves.

Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views.

Oracle Database queries are declarative. You can join JSON data with relational data. And you can project JSON data relationally, making it available for relational processes and tools. You can also query, from within the database, JSON data that is stored outside the database, in an external table.

You can access JSON data stored in the database the same way you access other database data, including using OCI, .NET, and JDBC.

Unlike XML data, which is stored using SQL data type XMLType, JSON data is stored in Oracle Database using SQL data types VARCHAR2, CLOB, and BLOB. Oracle recommends that you always use an is_json check constraint to ensure that column values are valid JSON instances (see Example 39-3).

JSON data in the database is textual, but the text can be stored using data type BLOB, as well as VARCHAR2 or CLOB.

Note:

Oracle recommends that whenever you store JSON data in a BLOB or CLOB column you turn on the LOB cache option for that column. This option is turned off by default. See Oracle Database SecureFiles and Large Objects Developer's Guide.

By definition, textual JSON data is encoded using a Unicode encoding, either UTF8 or UTF16. You can use textual data that is stored in a non-Unicode character set as if it were JSON data, but in that case Oracle Database automatically converts the character set to UTF8 when processing the data.

In SQL, you can access JSON data stored in Oracle Database using the following:

  • Functions json_value, json_query, and json_table.

  • Conditions json_exists, is json, is not json, and json_textcontains.

  • A dot notation that acts similar to a combination of json_value and json_query and resembles a SQL object access expression, that is, attribute dot notation for an abstract data type (ADT).

As a simple illustration, Example 39-2 uses the simple dot notation to query JSON column po_document for all purchase-order requestors (JSON object key Requestor).

Example 39-2 Simple SQL Query of JSON Data

SELECT po.po_document.Requestor FROM j_purchaseorder po;

Getting Started Using JSON with Oracle Database

In general, you will perform the following tasks when working with JSON data in Oracle Database:

  1. Create a relational table with a JSON column, and add an is json check constraint to ensure that the column contains only well-formed JSON data.

    The following statement creates relational table j_purchaseorder with JSON column po_document (see also Example 39-3):

    CREATE TABLE j_purchaseorder
       (id          RAW (16) NOT NULL,
        date_loaded TIMESTAMP WITH TIME ZONE,
        po_document CLOB
        CONSTRAINT ensure_json CHECK (po_document IS JSON));
    
  2. Insert JSON data into the JSON column, using any of the methods available for Oracle Database.

    The following statement uses a SQL INSERT statement to insert some simple JSON data. Some of the data is elided here (...). See Example 39-4 for these details.

    INSERT INTO j_purchaseorder
      VALUES (SYS_GUID(),
              SYSTIMESTAMP,
              '{"PONumber"             : 1600,
                "Reference"            : "ABULL-20140421",
                "Requestor"            : "Alexis Bull",
                "User"                 : "ABULL",
                "CostCenter"           : "A50",
                "ShippingInstructions" : {...}
                "Special Instructions" : null,
                "AllowPartialShipment" : true,
                "LineItems"            : [...]}');
    
  3. Query the JSON data. The return value is always a VARCHAR2 instance that represents a JSON value. Here are some simple examples.

    The following query extracts, from each JSON document in column po_document, a scalar value, the JSON number that is the value of property PONumber for the objects in JSON column po_document (see also Example 39-18):

    
    SELECT po.po_document.PONumber FROM j_purchaseorder po;
    

    The following query extracts, from each document in JSON column po_document, an array of JSON phone objects, which is the value of object property Phone of the value of object property ShippingInstructions (see also Example 39-19):

    
    SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;
    

    The following query extracts, from each JSON document, multiple values as an array: the value of object property type for each object in array Phone. The returned array is not part of the stored data but is constructed automatically by the query. (The order of the array elements is unspecified.)

    
    SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;
    

JSON: Character Sets and Character Encoding in Oracle Database

Textual JSON data always uses the Unicode character set. In this respect, JSON data is simpler to use than XML data. This is an important part of the JSON Data Interchange Format (RFC 4627).

Oracle Database uses UTF8 internally when it processes JSON data (parsing, querying). If the data that is input to such processing, or the data that is output from it, must be in a different character set from UTF8, then appropriate character-set conversion is carried out automatically.

Character-set conversion can affect performance. And in some cases it can be lossy: Conversion of input data to UTF8 is a lossless operation, but conversion to output can result in information loss in the case of characters that cannot be represented in the output character set.

If your textual JSON data is stored in the database as Unicode then no character-set conversion is needed. This is the case if the database character set is AL32UTF (Unicode UTF8). Oracle recommends this if at all possible.

JSON data that is not stored textually, that is, as characters, never undergoes character-set conversion: there are no characters to convert. This means that JSON data stored using data type BLOB suffers no character-set conversion.

If your JSON data is stored as non-Unicode character data, that is, using non-Unicode VARCHAR2 or CLOB storage, then consider doing the following to avoid character-set conversion:

See Also:

Escaping Unicode Characters in JSON Data

ASCII characters correspond directly to the first 128 Unicode characters. If your application can use an ASCII escape sequence to represent input Unicode characters that might otherwise require character-set conversion, then you will avoid any possible performance penalty or information loss from such conversion.

An ASCII escape sequence for a Unicode character is \u followed by four ASCII hexadecimal digits representing the Unicode code point for the character.

For example, the Unicode euro character , which is named EURO SIGN, has code point 20AC (using hexadecimal digits), so it can be represented using the ASCII escape sequence \u20AC.

You can use explicit ASCII escaping on input data. Because JSON data uses Unicode internally, when it is output, character-set conversion still applies, by default. However, for Oracle SQL functions json_value, json_query, and json_table you can use keyword ASCII to specify the automatic use of ASCII escape sequences for non-ASCII Unicode characters.

Oracle JSON Path Expressions

JSON is a notation for JavaScript values. When JSON data is stored in the database it is possible to query it using path expressions that are somewhat analogous to XQuery or XPath expressions for XML data. Similar to the way that SQL/XML allows SQL access to XML data using XQuery expressions, Oracle Database provides SQL access to JSON data using Oracle JSON path expressions.

Oracle JSON path expressions have a simple syntax. An Oracle JSON path expression selects zero or more JSON values that match, or satisfy, it.

Oracle SQL condition json_exists returns true if at least one value matches, and false if no value matches. If a single value matches, then SQL function json_value returns that value if it is scalar and raises an error if it is non-scalar. If no value matches the path expression then json_value returns SQL NULL.

Oracle SQL function json_query returns all of the matching values, that is, it can return multiple values. You can think of this behavior as returning a sequence of values, as in XQuery, or you can think of it as returning multiple values. (No user-visible sequence is manifested.)

In all cases, path-expression matching attempts to match each step of the path expression, in turn. If matching any step fails then no attempt is made to match the subsequent steps, and matching of the path expression fails. If matching each step succeeds then matching of the path expression succeeds.

See Also:

"Oracle JSON Path Expression Syntax" for information about path-expression steps

Oracle JSON Path Expression Syntax

Oracle JSON path expression syntax is an extension of JSON syntax. In particular, path expressions can use wildcards and array ranges.

You pass an Oracle JSON path expression and some JSON data to an Oracle SQL function or condition. The path expression is matched against the data, and the matching data is processed by the particular SQL function or condition. You can think of this matching process in terms of the path expression returning the matched data to the function or condition.

Oracle JSON Basic Path Expression Syntax

The basic syntax of an Oracle JSON path expression is as follows. However, this basic syntax is extended by relaxing the matching of arrays and non-arrays against non-array and array patterns, respectively: see "Oracle JSON Path Expression Syntax Relaxation".

  • An Oracle JSON path expression begins with a dollar sign ($), which represents the path-expression context item, that is, the JSON data to be matched. That data is the result of evaluating a SQL expression that is passed as argument to the Oracle SQL function.

    The dollar sign is followed by zero or more steps, each of which can be an object step or an array step, depending on whether the context item represents a JSON object or a JSON array.

  • An object step is a period (.), sometimes read as "dot", followed by an object property (key) name or an asterisk (*) wildcard, which stands for (the values of) all properties. A property name must start with an uppercase or lowercase letter A to Z and contain only such letters or decimal digits (0-9), or else it must be enclosed in double quotation marks ("). An object step returns the value of the property (key) that is specified. If a wildcard is used for the property then the step returns the values of all properties, in no special order.

  • An array step is a left bracket ([) followed by either an asterisk (*) wildcard, which stands for all array elements, or one or more specific array indexes or range specifications separated by commas, followed by a right bracket (]). In a path expression, array indexing is zero-based (0, 1, 2,...), as in the JavaScript convention for arrays. A range specification has the form N to M, where N and M are array indexes and N is strictly less than M. (An error is raised at query compilation time if N is not less than M.) An error is raised if you use both an asterisk and either an array index or range specification.

    When indexes or range specifications are used, the array elements they collectively specify must be specified in ascending order, without repetitions, or else a compile-time error is raised. For example, an error is raised for each of [3, 1 to 4], [4, 2], [2, 3 to 3], and [2, 3, 3], the first two because the order is not ascending and the last two because of the repeated element 3.

    Similarly, the elements in the array value that results from matching are in ascending order, with no repetitions. If an asterisk is used in the path expression then all of the array elements are returned, in array order.

Here are some examples of path expressions, with their meanings spelled out in detail.

  • $ – The context item.

  • $.friends – The value of property friends of the context-item object. The dotted notation indicates that the context item is a JSON object.

  • $.friends[0] – The object that is the first element of the array that is the value of property friends of the context-item object. The bracket notation indicates that the value of property friends is an array.

  • $.friends[0].name – Value of property name of the object that is the first element of the array that is the value of property friends of the context-item object. The second dot indicates that the first element of array friends is an object (with a name property).

  • $.friends[*].name – Value of property name of each object in the array that is the value of property friends of the context-item object.

  • $.*[*].name – Property name values for each object in an array value of a property of the context-item object.

  • $.friends[12, 3, 8 to 10] – The twelfth, third, eighth, ninth, and tenth elements of array friends (property of the context-item object). The elements are returned in array order: third, eighth, ninth, tenth, twelfth.

  • $friends[3].cars – The value of property cars of the object that is the third element of array friends. The dot indicates that the third element is an object (with a cars property).

  • $friends[3].* – The values of all of the properties of the object that is the third element of array friends.

  • $friends[3].cars[0].year – The value of property year of the object that is the first element of the array that is the value of property cars of the object that is the third element of the array that is bound to variable friends.

Oracle JSON Path Expression Syntax Relaxation

"Oracle JSON Basic Path Expression Syntax" defines the basic Oracle JSON path-expression syntax. The actual path expression syntax supported relaxes that definition as follows:

  • If a path-expression step targets (expects) an array but the actual data presents no array then the data is implicitly wrapped in an array.

  • If a path-expression step targets (expects) a non-array but the actual data presents an array then the array is implicitly unwrapped.

This relaxation allows for the following abbreviation: [*] can be elided whenever it precedes the object accessor, ., followed by an object property name, with no change in effect. The reverse is also true: [*] can always be inserted in front of the object accessor, ., with no change in effect.

This means that the object step [*].prop, which stands for the value of property prop of each element of a given array of objects, can be abbreviated as .prop, and the object step .prop, which looks as though it stands for the prop value of a single object, stands also for the prop value of each element of an array to which the object accessor is applied.

This is an important feature, because it means that you need not change a path expression in your code if your data evolves to replace a given JSON value with an array of such values, or vice versa.

For example, if your data originally contains objects that have property Phone whose value is a single object with properties type and number, the path expression $.Phone.number, which matches a single phone number, can still be used if the data evolves to represent an array of phones. Path expression $.Phone.number matches either a single phone object, selecting its number, or an array of phone objects, selecting the number of each.

Similarly, if your data mixes both kinds of representation — there are some data entries that use a single phone object and some that use an array of phone objects, or even some entries that use both — you can use the same path expression to access the phone information from these different kinds of entry.

Here are some example path expressions from section "Oracle JSON Basic Path Expression Syntax", together with an explanation of equivalences.

  • $.friends – The value of property friends of either:

    • The (single) context-item object.

    • (equivalent to $[*].friends) Each object in the context-item array.

  • $.friends[0].name – Value of property name for any of these objects:

    • The first element of the array that is the value of property friends of the context-item object.

    • (equivalent to $.friends.name) The value of property friends of the context-item object.

    • (equivalent to $[*].friends.name) The value of property friends of each object in the context-item array.

    • (equivalent to $[*].friends[0].name) The first element of each array that is the value of property friends of each object in the context-item array.

    The context item can be an object or an array of objects. In the latter case, each object in the array is matched for a property friends.

    The value of property friends can be an object or an array of objects. In the latter case, the first object in the array is used.

  • $.*[*].name – Value of property name for any of these objects:

    • An element of an array value of a property of the context-item object.

    • (equivalent to $.*.name) The value of a property of the context-item object.

    • (equivalent to $[*].*.name) The value of a property of an object in the context-item array.

    • (equivalent to $[*].*[*].name) Each object in an array value of a property of an object in the context-item array.

Oracle SQL Functions and Conditions for Use with JSON Data

This section describes Oracle SQL functions and conditions that you can use to create, query, and operate on JSON data stored in Oracle Database.

Some of these take as argument an Oracle JSON path expression as a literal SQL string, followed possibly by a RETURNING clause, a wrapper clause, or an error clause.

These are the Oracle SQL functions and conditions described here:

See Also:

Clauses Used in Oracle SQL Functions and Conditions for JSON

This section describes the RETURNING, wrapper, and error handling clauses used in one or more of the Oracle SQL functions and conditions json_value, json_query, json_table, is json, is not json, and json_exists.

RETURNING Clause for Oracle SQL Functions for JSON

The optional RETURNING clause specifies the data type of the value returned by the Oracle SQL function. In general, you can use the name of either of these SQL data types: VARCHAR2 or NUMBER. You can optionally specify a length for VARCHAR2 (default: 4000) and a precision and scale for NUMBER.

The default behavior (no RETURNING clause) is to use VARCHAR2(4000).

The RETURNING clause also accepts two optional keywords, PRETTY and ASCII. If both are present then PRETTY must come before ASCII. ASCII is allowed only for Oracle SQL functions json_value and json_query. PRETTY is allowed only for json_query.

The effect of keyword PRETTY is to pretty-print the returned data, by inserting newline characters and indenting. The default behavior is not to pretty-print.

The effect of keyword ASCII is to automatically escape all non-ASCII Unicode characters in the returned data, using standard ASCII Unicode escape sequences. The default behavior is not to escape non-ASCII Unicode characters.

Tip:

You can pretty-print the entire context item by using only $ as the path expression.

Wrapper Clause for Oracle SQL Functions JSON_QUERY and JSON_TABLE

The optional wrapper clause is only for Oracle SQL functions json_query and json_table. It specifies the form of the value returned by json_query or used for the data in a json_table relational column.

The wrapper clause takes one of these forms:

  • WITH WRAPPER – Use a string value that represents a JSON array containing all of the JSON values that match the path expression. The order of the array elements is unspecified.

  • WITHOUT WRAPPER – Use a string value that represents the single JSON object or array that matches the path expression. Raise an error if the path expression matches either a scalar value (not an object or array) or more than one value.

  • WITH CONDITIONAL WRAPPER – Use a string value that represents all of the JSON values that match the path expression. For zero values, a single scalar value, or multiple values, WITH CONDITIONAL WRAPPER is the same as WITH WRAPPER. For a single JSON object or array value, it is the same as WITHOUT WRAPPER.

The default behavior is WITHOUT WRAPPER.

You can add the optional keyword UNCONDITIONAL immediately after keyword WITH, if you find it clearer: WITH WRAPPER and WITH UNCONDITIONAL WRAPPER mean the same thing.

You can add the optional keyword ARRAY immediately before keyword WRAPPER, if you find it clearer: WRAPPER and ARRAY WRAPPER mean the same thing.

Table 39-1 illustrates the wrapper clause possibilities. The array wrapper is shown in bold.

Table 39-1 JSON_QUERY Wrapper Clause Examples

JSON Values Matching Path Expression WITH WRAPPER WITHOUT WRAPPER WITH CONDITIONAL WRAPPER

{"id": 38327} (single object)

[{"id": 38327}]

{"id": 38327}

{"id": 38327}

[42, "a", true] (single array)

[[42, "a", true]]

[42, "a", true]

[42, "a", true]

42

[42]

Error (scalar)

[42]

42, "a", true

[42, "a", true]

Error (multiple values)

[42, "a", true]

none

[]

Error (no values)

[]


Consider, for example, a json_query query to retrieve a JSON object. What happens if the path expression matches a JSON scalar value instead of an object, or it matches multiple JSON values (of any kind)? You might want to retrieve the matched values instead of raising an error. For example, you might want to pick one of the values that is an object, for further processing. Using an array wrapper lets you do this.

A conditional wrapper can be convenient if the only reason you are using a wrapper is to avoid raising an error and you do not need to distinguish those error cases from non-error cases. If your application is looking for a single object or array and the data matched by a path expression is just that, then there is no need to wrap that expected value in a singleton array.

On the other hand, with an unconditional wrapper you know that the resulting array is always a wrapper — your application can count on that. If you use a conditional wrapper then your application might need extra processing to interpret a returned array. In Table 39-1, for instance, note that the same array ([42, "a", true]) is returned for the very different cases of a path expression matching that array and a path expression matching each of its elements.

Error Clause for Oracle SQL Functions for JSON

The optional error clause specifies handling for an error that is raised by the Oracle SQL function or condition. The clause takes one of these forms:

  • ERROR ON ERROR – Raise the error (no special handling).

  • NULL ON ERROR – Return NULL instead of raising the error.

  • TRUE ON ERROR – Return true instead of raising the error. This form of the clause is available only for Oracle SQL condition json_exists.

  • FALSE ON ERROR – Return false instead of raising the error. This form of the clause is available only for Oracle SQL condition json_exists.

  • EMPTY ON ERROR – Return an empty array ([]) instead of raising the error. This form of the clause is available only for Oracle SQL function json_query.

  • DEFAULT 'literal_return_value' ON ERROR – Return the specified value instead of raising the error. The value must be a constant at query compile time. This form of the clause is not available for json_query.

The default behavior is NULL ON ERROR.

Note:

The ON ERROR clause takes effect only for runtime errors that arise when a syntactically correct Oracle JSON path expression is matched against JSON data. A path expression that is syntactically incorrect results in a compile-time syntax error; it is not handled by the ON ERROR clause.

Oracle SQL Conditions IS JSON and IS NOT JSON

Oracle SQL conditions is json and is not json are complementary. You can use them in a CASE expression or the WHERE clause of a SELECT statement.

They test whether their argument is syntactically correct, that is, well-formed, as JSON data. If so, then is json returns true and is not json returns false. If the argument cannot be evaluated for some reason (for example, if an error occurs during parsing) then the data is simply considered to not be well-formed: is json returns false; is not json returns true. Well-formedness implies that the data is syntactically correct. (JSON data can be well formed in two senses, which we refer to as strict and lax syntax.)

See Also:

Using a Check Constraint To Ensure that a Column Contains JSON Data

A typical use of is json is as a check constraint, to ensure that the data in a JSON column is (well-formed) JSON data.

Example 39-3 and Example 39-4 illustrate this. They create and fill a table that holds data used in the examples in this chapter.

For brevity, only one row of data (one JSON document) is inserted in Example 39-4. See "Loading External JSON Data" for examples that insert the full set of data.

Note:

Oracle recommends that you always use an is_json check constraint when you create a column intended for JSON data.

Example 39-3 Using IS JSON in a Check Constraint to Ensure JSON Data is Well-Formed

CREATE TABLE j_purchaseorder
   (id          RAW (16) NOT NULL,
    date_loaded TIMESTAMP (6) WITH TIME ZONE,
    po_document CLOB
    CONSTRAINT ensure_json CHECK (po_document IS JSON));

Example 39-4 Inserting Data into a Relational Table with a JSON Column

INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    SYSTIMESTAMP,
    '{"PONumber"             : 1600,
      "Reference"            : "ABULL-20140421",
      "Requestor"            : "Alexis Bull",
      "User"                 : "ABULL",
      "CostCenter"           : "A50",
      "ShippingInstructions" : {"name"   : "Alexis Bull",
                                "Address": {"street"  : "200 Sporting Green",
                                            "city"    : "South San Francisco",
                                            "state"   : "CA",
                                            "zipCode" : 99236,
                                            "country" : "United States of America"},
                                "Phone" : [{"type" : "Office", "number" : "909-555-7307"},
                                           {"type" : "Mobile", "number" : "415-555-1234"}]},
      "Special Instructions" : null,
      "AllowPartialShipment" : true,
      "LineItems"            : [{"ItemNumber" : 1,
                                 "Part"       : {"Description" : "One Magic Christmas",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 13131092899},
                                 "Quantity"   : 9.0},
                                {"ItemNumber" : 2,
                                 "Part"       : {"Description" : "Lethal Weapon",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 85391628927},
                                 "Quantity"   : 5.0}]}');

Note:

Oracle SQL conditions IS JSON and IS NOT JSON return true or false for any non-NULL SQL value. But they both return unknown (neither true nor false) for SQL NULL. When used in a check constraint, they do not prevent a NULL value from being inserted. But when used in a SQL WHERE clause, NULL is never selected (returned).

It is true that a check constraint can reduce performance for data insertion. If you are sure that your application inserts only well-formed JSON data into a particular column, then consider disabling the check constraint, but do not drop the constraint.

See Also:

Determining Whether a Column Necessarily Contains JSON Data

How can you tell whether a given column has a check constraint that ensures that its data is well-formed JSON data? Whenever this is the caseFoot 4 , the column is listed in the following static data dictionary views:

  • DBA_JSON_COLUMNS

  • USER_JSON_COLUMNS

  • ALL_JSON_COLUMNS

Each view lists the names of the owner, table, and column, as well as the data type of the column. You can query this data to find JSON columns.

Note that even if the check constraint that ensures that the data is JSON is deactivated, the column remains listed in the views. If the check constraint is dropped then the column is removed from the views.

Unique Versus Duplicate Keys in JSON Objects

The JSON standard does not specify whether property (key) names must be unique for a given JSON object. This means that, a priori, a well-formed JSON object can have multiple members that have the same key. This is the default behavior for handling JSON data in Oracle Database.

You can specify that particular JSON data is to be considered well-formed only if all objects it contains have unique keys, that is, no object has duplicate key names. You do this by using the keywords WITH UNIQUE KEYS with Oracle SQL condition is json. If you do not specify UNIQUE KEYS, or if you use the keywords WITHOUT UNIQUE KEYS, then objects can have duplicate key names and still be considered well-formed.

The evaluation that Oracle Database employs always uses only one of the object members that have a given key; any other members with the same key are ignored. It is unspecified which of multiple such members is used.

Whether duplicate keys are allowed in well-formed JSON data is orthogonal to whether Oracle uses strict or lax syntax to determine well-formedness.

About Strict and Lax JSON Syntax

Standard ECMA-404, the JSON Data Interchange Format, and ECMA-262, the ECMAScript Language Specification, define JSON syntax.

According to these specifications, each JSON property (key) name and each string value must be enclosed in double quotation marks ("). Oracle supports this strict JSON syntax, but it is not the default syntax.

In JavaScript notation, a property name used in an object literal can be, but need not be, enclosed in double quotation marks. It can also be enclosed in single quotation marks ('). Oracle also supports this lax JSON syntax, and it is the default syntax.

In addition, in practice, some JavaScript implementations (but not the JavaScript standard) allow one or more of the following:

  • Case variations for keywords true, false, and null (for example, TRUE, True, TrUe, fALSe, NulL).

  • An extra comma (,) after the last element of an array or the last member of an object (for example, [a, b, c,], {a:b, c:d,}).

  • Numerals with one or more leading zeros (for example, 0042.3).

  • Fractional numerals that lack 0 before the decimal point (for example, .14 instead of 0.14).

  • Numerals with no fractional part after the decimal point (for example, 342. or 1.e27).

  • A plus sign (+) preceding a numeral, meaning that the number is non-negative (for example, +1.3).

This syntax too is allowed as part of the Oracle default (lax) JSON syntax. (See the JSON standard for the strict numeral syntax.)

In addition to the ASCII space character (U+0020), the JSON standard defines the following characters as insignificant (ignored) whitespace when used outside a quoted property name or a string value:

  • Tab, horizontal tab (HT, ^I, decimal 9, U+0009, \t)

  • Line feed, newline (LF, ^J, decimal 10, U+000A, \n)

  • Carriage return (CR, ^M, decimal 13, U+000D, \r)

The Oracle JSON lax syntax, however, treats all of the ASCII control characters (Control+0 through Control+31), as well as the ASCII space character (decimal 32, U+0020), as (insignificant) whitespace characters. The following are among the control characters:

  • Null (NUL, ^@, decimal 0, U+0000, \0)

  • Bell (NEL, ^G, decimal 7, U+0007, \a)

  • Vertical tab (VT, ^K, decimal 11, U+000B)

  • Escape (ESC, ^[, decimal 27, U+001B, \e)

  • Delete (DEL, ^?, decimal 127, U+007F)

An ASCII space character (U+0020) is the only whitespace character allowed, unescaped, within a quoted property name or a string value. This is true for both the lax and strict Oracle JSON syntaxes.

For both strict and lax Oracle JSON syntax, quoted object property (key) names and string values can contain any Unicode character, but some of them must be escaped, as follows:

  • ASCII control characters are not allowed, except for those represented by the following escape sequences: \b (backspace), \f (form feed), \n (newline, line feed), \r (carriage return), and \t (tab, horizontal tab).

  • Double quotation mark ("), slash (/), and backslash (\) characters must also be escaped (preceded by a backslash): \", \/, and \\, respectively.

In the lax Oracle syntax, an object property name that is not quoted can contain any Unicode character except whitespace and the JSON structural characters — left and right brackets ([, ]) and curly braces ({, }), colon (:), and comma (,), but escape sequences are not allowed.

Any Unicode character can also be included in a name or string by using the ASCII escape syntax \u followed by the four ASCII hexadecimal digits that represent the Unicode code point.

Note that other Unicode characters that are not printable or that might appear as whitespace, such as a no-break space character (U+00A0), are not considered whitespace for either the strict or the lax Oracle JSON syntax.

Table 39-2 shows some examples of JSON syntax.

Table 39-2 JSON Object Property Name Syntax Examples

Example Well-Formed?

"part number": 1234

Lax and strict: yes. Space characters are allowed.

part number: 1234

Lax (and strict): no. Whitespace characters, including space characters, are not allowed in unquoted names.

"part\tnumber": 1234

Lax and strict: yes. Escape sequence for tab character is allowed.

"part    number": 1234

Lax and strict: no. Unescaped tab character is not allowed. Space is the only unescaped whitespace character allowed.

"\"part\"number": 1234

Lax and strict: yes. Escaped double quotation marks are allowed, if name is quoted.

\"part\"number: 1234

Lax and strict: no. Name must be quoted.

'\"part\"number': 1234

Lax: yes, strict: no. Single-quoted property names and strings are allowed for lax syntax only. Escaped double quotation mark is allowed in a quoted name.

"pärt number":1234

Lax and strict: yes. Any Unicode character is allowed in a quoted name.

part:number:1234

Lax (and strict): no. Structural characters are not allowed in unquoted names.

"pärt:number":1234

Lax: yes, strict: no. Structural and Unicode characters other than whitespace are allowed in a quoted name for lax syntax only.


Specifying Strict or Lax Oracle JSON Syntax

The default Oracle JSON syntax is lax. Strict or lax syntax matters only for conditions is json and is not json. All other Oracle SQL functions and conditions use lax syntax for interpreting input and strict syntax when returning output. If you need to be sure that particular JSON input data has strictly correct syntax, then check it first using is json or is not json.

You specify that data is to be checked as strictly well-formed according to the JSON standard by appending (STRICT) (parentheses included) to an is json or an is not json expression.

Example 39-5 illustrates this. It is identical to Example 39-3 except that it uses (STRICT) to ensure that all data inserted into the column is well-formed according to the JSON standard.

Example 39-5 Using IS JSON in a Check Constraint to Ensure JSON Data is Strictly Well-Formed (Standard)

CREATE TABLE j_purchaseorder
   (id          RAW (16) NOT NULL,
    date_loaded TIMESTAMP WITH TIME ZONE,
    po_document CLOB
    CONSTRAINT ensure_json CHECK (po_document IS JSON (STRICT)));

Oracle SQL Condition JSON_EXISTS

Oracle SQL condition json_exists lets you use an Oracle 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. It checks for the existence of a particular value within JSON data: it returns true if the value is present and false if it is absent.

More precisely, json_exists returns true if the data it targets matches one or more JSON values. If no JSON values are matched then it returns false.

You can also use json_exists to create bitmap indexes for use with JSON data — see Example 39-20.

Error handlers ERROR ON ERROR, FALSE ON ERROR, and TRUE ON ERROR apply. The default is FALSE ON ERROR. The handler takes effect when any error occurs, but typically an error occurs when the given JSON data is not well-formed (using lax syntax). Unlike the case for conditions is json and is not json, condition json_exists expects the data it examines to be well-formed JSON data.

The second argument to json_exists is an Oracle JSON path expression followed by an optional RETURNING clause and an optional error clause. The path expression must target a single scalar value, or else a compile-time error is raised.

Note:

Oracle SQL function json_exists applied to JSON value null returns the SQL string 'true'.

JSON_EXISTS as JSON_TABLE

Oracle SQL condition json_exists can be viewed as a special case of Oracle SQL function json_table. Example 39-6 illustrates the equivalence: the two SELECT statements have the same effect.

Example 39-6 JSON_EXISTS Expressed Using JSON_TABLE

SELECT select_list
  FROM table WHERE json_exists(column, json_path error_handler ON ERROR);
       
SELECT select_list
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" NUMBER EXISTS PATH json_path)) AS "JT"
  WHERE jt.column_alias = 1;

In addition to perhaps helping you understand json_exists better, this equivalence is important practically, because it means that you can use either to get the same effect.

In particular, if you use json_exists more than once, or you use it in combination with json_value or json_query (which can also be expressed using json_table), to access the same data then it is typically more efficient to use a single invocation of json_table instead, because the data is parsed only once.

Oracle SQL Function JSON_VALUE

Oracle SQL function json_value selects a scalar value from JSON data and returns it as a SQL value. You can also use json_value to create function-based B-tree indexes for use with JSON data — see "Indexing JSON Data".

Function json_value has two required arguments and accepts optional returning and error clauses.

The first argument to json_value is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the SQL expression is used as the context item for evaluating the path expression.

The second argument to json_value is an Oracle JSON path expression followed by an optional RETURNING clause and an optional error clause. The path expression must target a single scalar value, or else an error occurs.

Note that the default error-handling behavior is NULL ON ERROR, which means that no value is returned if an error occurs — an error is not raised. In particular, if the path expression targets a non-scalar value, such as an array, no error is raised, by default. To ensure that an error is raised, use ERROR ON ERROR.

Note:

Each key in a given JSON object is not necessarily unique; the same key may be repeated. The streaming evaluation that Oracle Database employs always uses only one of the object members that have a given key; any other members with the same key are ignored. It is unspecified which of multiple such members is used.

See also "Unique Versus Duplicate Keys in JSON Objects".

Using Oracle SQL Function JSON_VALUE With a Boolean JSON Value

JSON has the Boolean values true and false. Oracle SQL has no Boolean data type. When Oracle SQL function json_value evaluates an Oracle JSON path expression and the result is true or false, there are two ways to handle the result in SQL.

By default, the returned data type is a SQL string (VARCHAR2), meaning that the result is the string 'true' or 'false'. You can alternatively return the result as a SQL number, in which case the JSON value true is returned as the number 1, and false is returned as 0.

Example 39-7 illustrates this. The first query returns the string 'true'; the second query returns the number 1.

Example 39-7 JSON_VALUE: Two Ways to Return a JSON Boolean Value in SQL

SELECT json_value(po_document, '$.AllowPartialShipment')
 FROM j_purchaseorder;

SELECT json_value(po_document, '$.AllowPartialShipment' RETURNING NUMBER)
  FROM j_purchaseorder;

Note:

Although you can return a Boolean value as a number, a JSON search index will not be picked up for such a value. For this reason, Oracle recommends that you use the default return-value data type, VARCHAR2.

Using Oracle SQL Function JSON_VALUE With a null JSON Value

Oracle SQL function json_value applied to JSON value null returns SQL NULL, not the SQL string 'null'. This means, in particular, that you cannot use json_value to distinguish the JSON value null from the absence of a value; SQL NULL indicates both cases.

JSON_VALUE as JSON_TABLE

Oracle SQL function json_value can be viewed as a special case of function json_table. Example 39-8 illustrates the equivalence: the two SELECT statements have the same effect.

Example 39-8 JSON_VALUE Expressed Using JSON_TABLE

SELECT json_value(column, json_path RETURNING data_type error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";

In addition to perhaps helping you understand json_value better, this equivalence is important practically, because it means that you can use either function to get the same effect.

In particular, if you use json_value more than once, or you use it in combination with json_exists or json_query (which can also be expressed using json_table), to access the same data then it is typically more efficient to use a single invocation of json_table instead, because the data is parsed only once.

Oracle SQL Function JSON_QUERY

Oracle SQL function 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.

The first argument to json_query is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the SQL expression is used as the context item for evaluating the path expression.

The second argument to json_query is an Oracle JSON path expression followed by an optional RETURNING clause, an optional wrapper clause, and an optional error clause. The path expression can target any number of JSON values.

In the RETURNING clause you can specify only data type VARCHAR2; you cannot specify NUMBER.

The wrapper clause determines the form of the returned string value.

Note that the error clause for json_query can specify EMPTY ON ERROR, which means that an empty array ([]) is returned in case of error (no error is raised).

Example 39-9 shows an example of the use of Oracle SQL function json_query with an array wrapper. For each document it returns a VARCHAR2 value whose contents represent a JSON array with elements the phone types, in an unspecified order. For the document in Example 39-4 the phone types are "Office" and "Mobile", and the array returned is either ["Mobile", "Office"] or ["Office", "Mobile"].

Example 39-9 Selecting JSON Values Using JSON_QUERY

SELECT json_query(po_document, '$.ShippingInstructions.Phone[*].type'
                               WITH WRAPPER)
  FROM j_purchaseorder;

Note that if path expression $.ShippingInstructions.Phone.type were used in Example 39-9 it would give the same result. Because of Oracle JSON path-expression syntax relaxation, [*].type is equivalent to .type.

JSON_QUERY as JSON_TABLE

Oracle SQL function json_query can be viewed as a special case of function json_table. Example 39-10 illustrates the equivalence: the two SELECT statements have the same effect.

Example 39-10 JSON_QUERY Expressed Using JSON_TABLE

SELECT json_query(column, json_path
                  RETURNING data_type array_wrapper error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type FORMAT JSON array_wrapper
                  PATH json_path)) AS "JT";

In addition to perhaps helping you understand json_query better, this equivalence is important practically, because it means that you can use either function to get the same effect.

In particular, if you use json_query more than once, or you use it in combination with json_exists or json_value (which can also be expressed using json_table), to access the same data then it is typically more efficient to use a single invocation of json_table instead, because the data is parsed only once.

Oracle SQL Function JSON_TABLE

Oracle SQL function json_table projects JSON data into a relational format. You use json_table to decompose the result of JSON expression evaluation into the relational rows and columns of a new, virtual table, which you can also think of as an inline relational view. You can then insert this virtual table into a pre-existing database table, or you can query it using SQL — in a join expression, for example.

In particular, a common use of json_table is to create a relational view of JSON data. You can use such a view just as you would use any relational table or view. This lets applications, tools, and programmers operate on JSON data as if it were relational, that is, without consideration of the syntax of JSON or JSON path expressions.

Defining a relational view over JSON data in effect maps a kind of schema onto that data. This mapping is after the fact: the underlying JSON data can be defined and created without any regard to a schema or any particular pattern of use. Data first, schema later.

Such a schema (mapping) imposes no restriction on the kind of JSON documents that can be stored in the underlying table (other than being well-formed JSON data). The relational view exposes only data that conforms to the mapping (schema) that defines the view. To change the schema, just redefine the view — no need to reorganize the underlying JSON data. Example 39-17 illustrates the creation of a relational view using json_table.

You use json_table in a SQL FROM clause. It is thus a row source: it generates a row of relational data for each JSON value selected by a row path expression (row pattern).

The rows created by a json_table invocation are laterally joined, implicitly, to the row that generated them. That is, you need not explicitly join the virtual table produced by json_table with the table that contains the JSON data.

The first argument to json_table is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the SQL expression is used as the context item for evaluating the row path expression.

The second argument to json_table is an Oracle JSON row path expression followed by an optional error clause for handling the row and a (required) COLUMNS clause. (There is no RETURNING clause.) The path expression can target any number of JSON values.

The row path expression acts as a pattern for the rows of the generated virtual table. It is matched against the context item provided by the SQL FROM clause, producing rows of SQL data that are organized into relational columns, which you specify in the COLUMNS clause. Each of those rows is matched against zero or more column path expressions to generate the relational columns of the virtual table.

There are two levels of error handling for json_table, corresponding to the two levels of path expressions: row and column. When present, a column error handler overrides row-level error handling. The default error handler for both levels is NULL ON ERROR.

The mandatory COLUMNS clause defines the columns of the virtual table to be created by json_table. It consists of the keyword COLUMNS followed by the following entries enclosed in parentheses:

  • At most one entry in the COLUMNS clause can be a column name followed by the keywords FOR ORDINALITY, which specifies a column of generated row numbers (SQL data type NUMBER). These numbers start with one.

  • Other than the optional FOR ORDINALITY entry, each entry in the COLUMNS clause is either a regular column specification or a nested columns specification.

  • A regular column specification consists of a column name followed by an optional scalar data type for the column, which can be SQL data type VARCHAR2 or NUMBER (the same as for the RETURNING clause of other Oracle SQL functions for JSON), followed by an optional value clause and a mandatory PATH clause. The default data type is VARCHAR2(4000).

  • A nested columns specification consists of the keyword NESTED followed by an optional PATH keyword, an Oracle JSON row path expression, and then a COLUMNS clause. This COLUMNS clause specifies columns that represent nested data. The row path expression used here provides a refined context for the specified nested columns: each nested column path expression is relative to the row path expression.

    A COLUMNS clause at any level (nested or not) has the same characteristics. In other words, COLUMNS clause is defined recursively. For each level of nesting (that is, for each use of keyword NESTED), the nested COLUMNS clause is said to be the child of the COLUMNS clause within which it is nested, which is its parent. Two or more COLUMNS clauses that have the same parent clause are siblings.

    The virtual tables defined by parent and child COLUMNS clauses are joined using an outer join, with the parent being the outer table. The virtual columns defined by sibling COLUMNS clauses are joined using a union join.

    Example 39-16 illustrates the use of a nested columns clause.

  • The optional value clause specifies how to handle the data projected to the column: whether to handle it as would json_value, json_exists, or json_query. This value handling includes the return data type, return format (pretty or ascii), wrapper, and error treatment.

    By default, the projected data is handled as if by json_value. If you use keyword EXISTS then it is handled as if by json_exists. If you use keywords FORMAT JSON then it is handled as if by json_query.

    For FORMAT JSON you can override the default wrapping behavior by adding an explicit wrapper clause.

    You can override the default error handling for the given handler (json_value, json_exists, or json_query) by adding an explicit ERROR clause appropriate for it.

  • The mandatory PATH clause specifies the portion of the row that is to be used as the column content. The column path expression following keyword PATH is matched against the context item provided by the virtual row. The column path expression must represent a relative path; it is relative to the path specified by the row path expression.

JSON_TABLE Generalizes Other Oracle SQL Functions

Oracle SQL function json_table generalizes functions json_value, json_exists, and json_query. Everything that you can do using these functions you can do using json_table. For the jobs that they do, the syntax of these functions is simpler to use than is the syntax of json_table.

However, be aware that if you use such functions to access the same input data, that data is parsed once for each use of one of the functions. It can be more efficient for such multiple accesses of the same data to use json_table instead, so the data is parsed only once.

Example 39-11 and Example 39-12 illustrate this. They each select the requestor and the set of phones used by each object in column j_purchaseorder.po_document. But Example 39-12 parses that column only once, not four times.

Example 39-11 Accessing JSON Data Multiple Times To Extract Data

SELECT json_value(po_document, '$.Requestor' RETURNING VARCHAR2(32)),
       json_query(po_document, '$.ShippingInstructions.Phone'
                               RETURNING VARCHAR2(100))
  FROM j_purchaseorder
  WHERE json_exists(po_document, '$.ShippingInstructions.Address.zipCode')
    AND json_value(po_document,  '$.AllowPartialShipment' RETURNING NUMBER(1))
        = 0;

Example 39-12 Using JSON_TABLE to Extract Data Without Multiple Parses

SELECT jt.requestor, jt.phones
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
                  phones    VARCHAR2(100 CHAR) FORMAT JSON
                            PATH '$.ShippingInstructions.Phone',
                  partial   NUMBER(1) PATH '$.AllowPartialShipment',
                  has_zip   VARCHAR2(5 CHAR) EXISTS
                            PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial = 0 AND has_zip = 'true';

Note the following in connection with Example 39-12:

  • A JSON value of null is a value as far as SQL is concerned; it is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data). In Example 39-12, if the JSON value of object attribute zipCode is null then the SQL string 'true' is returned.

  • Although json_exists returns a Boolean value, as a SQL value this is represented by the SQL string 'true' or 'false'. If json_exists is used directly as a condition in a SQL WHERE clause or CASE statement then you need not test this return value explicitly; you can simply write json_exists(...). But if json_exists is used elsewhere, to obtain a value, then the only way to test that value is as an explicit string. That is the case in Example 39-12: the value is stored in column jt.has_zip, and it is then tested explicitly for equality against the SQL string 'true'.

  • The JSON object attribute AllowPartialShipment has a JSON Boolean value. When json_value is applied to that value it can be returned as either a string or a number. In Example 39-12 the implicit use of json_value returns the value as data type NUMBER, and this value is then tested for equality against the number 1.

Using JSON_TABLE with JSON Arrays

A JSON value can be an array or can include one or more arrays, nested to any number of levels inside other JSON arrays or objects.

Example 39-13 projects the requestor and associated phone numbers from the JSON data in column po_document. The entire JSON array Phone is projected as a relational column of JSON data, ph_arr. To format this JSON data as a VARCHAR2 column, the keywords FORMAT JSON are needed.

Example 39-13 Projecting an Entire JSON Array as JSON Data

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
                  ph_arr    VARCHAR2(100 CHAR) FORMAT JSON
                            PATH '$.ShippingInstructions.Phone')) AS "JT";

What if you wanted to project the individual elements of JSON array Phone and not the array as a whole? Example 39-14 shows one way to do this, which you can use if the array elements are the only data you need to project.

Example 39-14 Projecting Elements of a JSON Array

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$.ShippingInstructions.Phone[*]'
         COLUMNS (phone_type VARCHAR2(10) PATH '$.type',
                  phone_num VARCHAR2(20) PATH '$.number')) AS "JT";

PHONE_TYPE     PHONE_NUM
----------     ---------
Office         909-555-7307
Mobile         415-555-1234

If you want to project both the requestor and the corresponding phone data then the row path expression of Example 39-14 ($.Phone[*]) is not appropriate: it targets only the (phone object) elements of array Phone.

Example 39-15 shows one way to target both: use a row path expression that targets both the name and the entire phones array, and use column path expressions that target properties type and number of individual phone objects.

Example 39-15 Projecting Elements of a JSON Array Plus Other Data

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (
           requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
           phone_type VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
                      PATH '$.ShippingInstructions.Phone[*].type',
           phone_num  VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
                      PATH '$.ShippingInstructions.Phone[*].number')) AS "JT";

REQUESTOR    PHONE_TYPE            PHONE_NUM
---------    ----------            ---------
Alexis Bull  ["Office", "Mobile"]  ["909-555-7307", "415-555-1234"]

In Example 39-15 as in Example 39-13, keywords FORMAT JSON are needed because the resulting VARCHAR2 columns contain JSON data, namely arrays of phone types or phone numbers, with one array element for each phone. In addition, unlike the case for Example 39-13, a wrapper clause is needed for column phone_type and column phone_num, because array Phone contains multiple objects with properties type and number.

The effect of Example 39-15 might not be what you want in some cases. For example, you might want a relational column that contains a single phone number (one row per number), rather than one that contains a JSON array of phone numbers (one row for all numbers for a given purchase order).

For that you need to tell json_table to project the array elements, by using a json_table NESTED path clause for the array. A NESTED path clause acts, in effect, as an additional row source (row pattern). Example 39-16 illustrates this.

Example 39-16 JSON_TABLE: Projecting Array Elements Using NESTED

SELECT jt.*
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (
           requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
           NESTED                      PATH '$.ShippingInstructions.Phone[*]'
             COLUMNS (phone_type VARCHAR2(32 CHAR) PATH '$.type',
                      phone_num  VARCHAR2(20 CHAR) PATH '$.number'))) AS "JT";

You can use any number of NESTED keywords in a given json_table invocation.

In Example 39-16 the outer COLUMNS clause is the parent of the nested (inner) COLUMNS clause. The virtual tables defined are joined using an outer join, with the table defined by the parent clause being the outer table in the join.

(If there were a second columns clause nested directly under the same parent, the two nested clauses would be sibling COLUMNS clauses.)

Example 39-17 defines a relational view over JSON data. It uses a NESTED path clause to project the elements of array LineItems.

Example 39-17 Defining a Relational View Over JSON Data

CREATE OR REPLACE VIEW j_purchaseorder_detail_view AS
  SELECT d.*
    FROM j_purchaseorder po,
         json_table(po.po_document, '$'
           COLUMNS (
             po_number        NUMBER(10)         PATH '$.PONumber',
             reference        VARCHAR2(30 CHAR)  PATH '$.Reference',
             requestor        VARCHAR2(128 CHAR) PATH '$.Requestor',
             userid           VARCHAR2(10 CHAR)  PATH '$.User',
             costcenter       VARCHAR2(16)       PATH '$.CostCenter',
             ship_to_name     VARCHAR2(20 CHAR)
                              PATH '$.ShippingInstructions.name',
             ship_to_street   VARCHAR2(32 CHAR)
                              PATH '$.ShippingInstructions.Address.street',
             ship_to_city     VARCHAR2(32 CHAR)
                              PATH '$.ShippingInstructions.Address.city',
             ship_to_county   VARCHAR2(32 CHAR)
                              PATH '$.ShippingInstructions.Address.county',
             ship_to_postcode VARCHAR2(10 CHAR)
                              PATH '$.ShippingInstructions.Address.postcode',
             ship_to_state    VARCHAR2(2 CHAR)
                              PATH '$.ShippingInstructions.Address.state',
             ship_to_zip      VARCHAR2(8 CHAR)
                              PATH '$.ShippingInstructions.Address.zipCode',
             ship_to_country  VARCHAR2(32 CHAR)
                              PATH '$.ShippingInstructions.Address.country',
             ship_to_phone    VARCHAR2(24 CHAR)
                              PATH '$.ShippingInstructions.Phone[0].number',
             NESTED PATH '$.LineItems[*]'
               COLUMNS (
                 itemno      NUMBER(38)         PATH '$.ItemNumber', 
                 description VARCHAR2(256 CHAR) PATH '$.Part.Description', 
                 upc_code    VARCHAR2(14 CHAR)  PATH '$.Part.UPCCode', 
                 quantity    NUMBER(12,4)       PATH '$.Quantity', 
                 unitprice   NUMBER(14,2)       PATH '$.Part.UnitPrice'))) d;

Simple Dot-Notation Access to JSON Data

Oracle SQL functions json_query and json_value accept an Oracle JSON path expression as argument and match it against the target JSON data. These functions accept optional returning, wrapper, and error handling clauses, to specify the following, respectively: the data type of the return value, whether or not to wrap multiple values as an array, and how to handle errors.

As an alternative for simple use cases, you can use a dot-notation syntax to query JSON data without using json_query or json_value. This section describes this feature.

The behavior of a query using the dot notation is different from both json_query and json_value. In effect, it combines their behavior to return one or more JSON values whenever possible.

Where one or the other of these SQL functions might return NULL or raise an error because the path expression does not match the JSON data, a dot-notation query often returns a useful JSON value. The return value is always a string (data type VARCHAR2) representing JSON data. The content of the string depends on the targeted JSON data, as follows:

  • If a single JSON value is targeted, then that value is the string content, whether it is a JSON scalar, object, or array.

  • If multiple JSON values are targeted, then the string content is a JSON array whose elements are those values.

In the first case, the behavior is similar to that of json_value for a scalar value, and it is similar to that of json_query for an object or array value.

In the second case, the behavior is similar to that of json_query with an array wrapper.

The dot-notation syntax is a table alias followed by a dot (.), the name of a JSON column, and one or more pairs of the form . json_object_key. (Note that the table alias is mandatory.)

Each json_object_key must be a valid SQL identifier, and the column must have an is json check constraint, which ensures that it contains well-formed JSON data. If either of these rules is not respected then an error is raised at query compile time. (The check constraint must be present to avoid raising an error; however, it need not be active. If you deactivate the constraint then this error is not raised.)

For this JSON dot notation, unlike the case generally for SQL, unquoted identifiers (after the column name) are treated case sensitively, that is, just as if they were quoted. This is a convenience: you can use JSON object keys as identifiers here without quoting them. For example, you can write just jcolumn.friends instead of jcolumn."friends". And if a JSON object is named using uppercase, such as FRIENDS, then you must write jcolumn.FRIENDS, not jcolumn.friends.

Note:

Each component of the dot-notation syntax is limited to a maximum of 30 bytes. See Oracle Database SQL Language Reference for more information about the dot-notation syntax and SQL identifiers.

Matching of a JSON dot-notation expression against JSON data is the same as for an Oracle JSON path expression, including the relaxation to allow implied array iteration (see "Oracle JSON Path Expression Syntax Relaxation"). The JSON column of a dot-notation expression corresponds to the context item of a path expression, and each identifier used in the dot notation corresponds to an identifier used in a path expression.

For example, if JSON column jcolumn corresponds to the path-expression context item, then dot-notation expression jcolumn.friends corresponds to path expression $.friends, and jcolumn.friends.name corresponds to $.friends.name.

For the latter, the context item can be an object or an array of objects. If it is an array of objects then each of the objects is matched for a property friends. The value of property friends can itself be an object or an array of objects. In the latter case, the first object in the array is used.

Other than the implied use of a wildcard for array elements (see "Oracle JSON Path Expression Syntax Relaxation"), a path expression with wildcards cannot be expressed using the dot-notation syntax. Dot-notation syntax is a handy alternative to using simple path expressions; it is not a replacement for using path expressions in general.

Example 39-18 shows equivalent dot-notation and json_value queries. Given the data from Example 39-4, each of the queries returns the string "1600", a VARCHAR2 value representing the JSON number 1600.

Example 39-18 JSON Dot-Notation Query Compared with JSON_VALUE

SELECT po.po_document.PONumber FROM j_purchaseorder po;

SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;

Example 39-19 shows equivalent dot-notation and json_query queries. Each query in the first pair returns (a VARCHAR2 value representing) a JSON array of phone objects. Each query in the second pair returns (a VARCHAR2 value representing) an array of phone types, just as in Example 39-9.

Example 39-19 JSON Dot-Notation Query Compared with JSON_QUERY

SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;

SELECT json_query(po_document, '$.ShippingInstructions.Phone')
  FROM j_purchaseorder;

SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;

SELECT json_query(po_document, '$.ShippingInstructions.Phone.type' WITH WRAPPER)
  FROM j_purchaseorder;

Indexing JSON Data

There is no dedicated SQL data type for JSON data. You can index JSON data as you would any data of the type you use to store it. This means, in particular, that you can use a B-tree index or a bitmap index for Oracle SQL function json_value, and you can use a bitmap index for Oracle SQL conditions is json, is not json, and json_exists.

(More generally, a bitmap index can be appropriate wherever the number of possible values for the function is small. For example, you can use a bitmap index for function json_value if the value is expected to be Boolean or otherwise one of a small number of string values.)

As always, such function-based indexing is appropriate for queries that target particular functions, which in the context of Oracle SQL functions for JSON means particular path expressions. It is not very helpful for queries that are ad hoc, that is, arbitrary. Define a function-based index if you know that you will often query a particular JSON path expression.

If you query in an ad hoc manner then define a JSON search index. This is a general index, not targeted to any specific JSON path expression. It is appropriate for structural queries, such as looking for the third element of an array value of a particular object property, and for full-text queries using Oracle SQL condition json_textcontains, such as looking for a particular word among various string values.

You can of course define both function-based indexes and a JSON search index for the same JSON column.

A JSON search index is an Oracle Text (full-text) index designed specifically for use with JSON data.

See Also:

"Full-Text Search of JSON Data" for information about creating and using a JSON search index

How To Tell Whether a Function-Based Index for JSON Data Is Picked Up

To determine whether a given query picks up a given function-based index, look for the index name in the execution plan for the query. For example, given the index defined in Example 39-22, an execution plan for the json_value query of Example 39-18 references an index scan with index po_num_id1.

Creating Bitmap Indexes for Oracle SQL Condition JSON_EXISTS

Example 39-20 creates a bitmap index for the value returned by json_exists. This is the right kind of index to use because there are only two possible return values for a condition (true and false).

Example 39-20 Creating a Bitmap Index for JSON_EXISTS

CREATE BITMAP INDEX has_zipcode_idx
  ON j_purchaseorder (json_exists(po_document, 
                                  '$.ShippingInstructions.Address.zipCode'));

Example 39-21 creates a bitmap index for a value returned by json_value. This is an appropriate index to use if there are only few possible values for property CostCenter in your data.

Example 39-21 Creating a Bitmap Index for JSON_VALUE

CREATE BITMAP INDEX cost_ctr_idx
  ON j_purchaseorder (json_value(po_document, '$.CostCenter'));

Creating JSON_VALUE Function-Based Indexes

Example 39-22 creates a function-based index for SQL function json_value on property PONumber of the object that is in column po_document of table j_purchaseorder. The object is passed as the path-expression context item.

Example 39-22 Creating a Function-Based Index for a JSON Object Property: JSON_VALUE

CREATE UNIQUE INDEX po_num_idx1
  ON j_purchaseorder (json_value(po_document, '$.PONumber' 
                                 RETURNING NUMBER ERROR ON ERROR));

The use of ERROR ON ERROR here means that if the data contains a record that either has no PONumber property or has a PONumber property with a non-number value then index creation fails. And if the index exists then trying to insert such a record fails.

An alternative is to create an index using the simplified syntax described in "Simple Dot-Notation Access to JSON Data". Example 39-23 illustrates this; it indexes both scalar and non-scalar results, corresponding to what a dot-notation query can return.

Example 39-23 Creating a Function-Based Index for a JSON Object Property: Dot Notation

CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder po (po.po_document.PONumber);

The indexes created in both Example 39-22 and Example 39-23 can be picked up for either a query that uses dot-notation syntax or a query that uses json_value.

If the index of Example 39-23 is picked up for a json_value query then filtering is applied after index pickup, to test for the correct property value. Non-scalar values can be stored in this index, since dot-notation queries can return such values, but a json_value query cannot, so such values are filtered out after index pickup.

Oracle recommends that you create a function-based index for json_value using one of these forms:

  • A json_value expression that specifies a RETURNING data type and uses ERROR ON ERROR.

    The indexed values are only (non-null) scalar values of the specified data type. The index can nevertheless be used in dot-notation queries that lead to such a scalar result.

  • Dot-notation syntax

    The indexed values correspond to the flexible behavior of dot-notation queries, which return JSON values whenever possible. They can include non-scalar JSON values (JSON objects and arrays). They can match dot-notation queries in addition to json_value queries. The index is used to come up with an initial set of matches, which are then filtered according to the specifics of the query. For example, any indexed values that are not JSON scalars are filtered out.

Indexes created in both of these ways can thus be used with both dot-notation queries and json_value queries.

Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries

An index created using json_value with ERROR ON ERROR can be used for a query involving json_table, if the WHERE clause refers to a column projected by json_table and the effective JSON path that targets that column matches the indexed path expression. The index acts as a constraint on the indexed path, to ensure that only one (non-null) scalar JSON value is projected for each item in the JSON collection.

The query in Example 39-24 thus makes use of the index created in Example 39-22.

Example 39-24 Use of a JSON_VALUE Function-Based Index with a JSON_TABLE Query

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.po_document, '$'
         COLUMNS po_number  NUMBER(5) PATH '$.PONumber',
                 reference  VARCHAR2(30 CHAR) PATH '$.Reference',
                 requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
                 userid     VARCHAR2(10 CHAR) PATH '$.User',
                 costcenter VARCHAR2(16 CHAR) PATH '$.CostCenter') jt
  WHERE po_number = 1600;

Note:

A function-based index created using a json_value expression or dot notation can be picked up for a correponding occurrence in a query WHERE clause only if the occurrence is used in a SQL comparison condition, such as >=. In particular, it is not picked up for an occurrence used in condition IS NULL or IS NOT NULL.

See Oracle Database SQL Language Reference for information about SQL comparison conditions.

Data Type Considerations for JSON_VALUE Indexing and Querying

By default, the Oracle SQL functions for JSON return a VARCHAR2 value. When you create a function-based index using json_value, unless you use a RETURNING clause to specify a different return data type, the index is not picked up for a query that expects a non-VARCHAR2 value.

For example, in the query of Example 39-25, json_value uses RETURNING NUMBER. The index created in Example 39-22 can be picked up for this query, because the indexed json_value expression specifies a return type of NUMBER.

Example 39-25 JSON_VALUE Query with Explicit RETURNING NUMBER

SELECT count(*) FROM j_purchaseorder po  WHERE json_value(po_document, '$.PONumber' RETURNING NUMBER) > 1500;

But the index created in Example 39-23 does not use RETURNING NUMBER (the return type is VARCHAR2(4000), by default), so it cannot be picked up for a such a query.

Now consider the queries in Example 39-26 and Example 39-27, which use json_value without a RETURNING clause, so that the value returned is of type VARCHAR2.

Example 39-26 JSON_VALUE Query with Explicit Numerical Conversion

SELECT count(*) FROM j_purchaseorder po  WHERE to_number(json_value(po_document, '$.PONumber')) > 1500;

Example 39-27 JSON_VALUE Query with Implicit Numerical Conversion

SELECT count(*) FROM j_purchaseorder po  WHERE json_value(po_document, '$.PONumber') > 1500;

In Example 39-26, SQL function to_number explicitly converts the VARCHAR2 value returned by json_value to a number. Similarly, in Example 39-27, comparison condition > (greater-than) implicitly converts the value to a number.

Neither of the indexes of Example 39-22 and Example 39-23 is picked up for either of these queries. The queries might return the right results in each case, because of type-casting, but the indexes cannot be used to evaluate the queries.

Consider also what happens if some of the data cannot be converted to a particular data type. For example, given the queries in Example 39-25, Example 39-26, and Example 39-27, what happens to a PONumber value such as "alpha"?

For Example 39-26 and Example 39-27, the query stops in error because of the attempt to cast the value to a number. For Example 39-25, however, because the default error handling behavior is NULL ON ERROR, the non-number value "alpha" is simply filtered out. The value is indexed, but it is ignored for the query.

Similarly, if the query used, say, DEFAULT '1000' ON ERROR, that is, if it specified a numeric default value, then no error would be raised for the value "alpha": the default value of 1000 would be used.

Indexing Multiple JSON Properties Using a Composite B-Tree Index

To index multiple properties of a JSON object you first create virtual columns for them. Then you create a composite B-tree index on the virtual columns. Example 39-28 and Example 39-29 illustrate this. Example 39-28 creates virtual columns userid and costcenter for JSON object properties User and CostCenter, respectively.

Example 39-28 Creating Virtual Columns for JSON Object Properties

ALTER TABLE j_purchaseorder ADD (userid VARCHAR2(20)
  GENERATED ALWAYS AS (json_value(po_document, '$.User' RETURNING VARCHAR2(20))));

ALTER TABLE j_purchaseorder ADD (costcenter VARCHAR2(6)
  GENERATED ALWAYS AS (json_value(po_document, '$.CostCenter'
                       RETURNING VARCHAR2(6))));

Example 39-29 creates a composite B-tree index on the virtual columns of Example 39-28.

Example 39-29 Creating a Composite B-tree Index for JSON Object Properties

CREATE INDEX user_cost_ctr_idx on j_purchaseorder(userid, costcenter);

A SQL query that references either the virtual columns or the corresponding JSON data (object properties) picks up the composite index. This is the case for both of the queries in Example 39-30.

Example 39-30 Two Ways to Query JSON Data Indexed with a Composite Index

SELECT po_document FROM j_purchaseorder WHERE userid      = 'ABULL'
                                          AND costcenter  = 'A50';

SELECT po_document
  FROM j_purchaseorder WHERE json_value(po_document, '$.User')       = 'ABULL'
                         AND json_value(po_document, '$.CostCenter') = 'A50';

These two queries have the same effect, including the same performance. However, the first query form does not target the JSON data itself; it targets the virtual columns that are used to index that data.

The data does not depend logically on any indexes implemented to improve query performance. If you want this independence from implementation to be reflected in your code, then use the second query form. Doing that ensures that the query behaves the same functionally with or without the index — the index serves only to improve performance.

Full-Text Search of JSON Data

You can use Oracle SQL condition json_textcontains in a CASE expression or the WHERE clause of a SELECT statement to perform a full-text search of JSON data that is stored in a VARCHAR2, BLOB, or CLOB column.

To be able to use condition json_textcontains, you first must create a JSON search index, which is an Oracle Text index designed specifically for use with JSON data. If you do not, then an error is raised when json_textcontains is used.

A JSON search index is appropriate for general, ad hoc queries of JSON data, whether or not those queries make use of full-text search.

Note:

A JSON search index can only be used when the database character set is AL32UTF8 or WE8ISO8859P1, and only for JSON data that uses VARCHAR2, BLOB, or CLOB storage. Otherwise, the index can be created but it has no effect on queries.

You create a JSON search index by specifying an index type of CTXSYS.CONTEXT and section group CTXSYS.JSON_SECTION_GROUP using a PARAMETERS clause. Example 39-31 illustrates this.

Example 39-31 Creating a JSON Search Index

CREATE INDEX po_search_idx ON j_purchaseorder (po_document)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Example 39-32 shows a full-text query that finds purchase-order documents that contain the keyword Magic in any of the line-item part desciptions.

Example 39-32 Full-Text Query of JSON Data

SELECT po_document FROM j_purchaseorder
  WHERE json_textcontains(po_document, '$.LineItems.Part.Description', 'Magic');

Example 39-33 shows some non full-text queries of JSON data that also make use of the JSON search index created in Example 39-31.

Example 39-33 Ad Hoc Queries of JSON Data

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document, '$.ShippingInstructions.Address.country');

SELECT po_document FROM j_purchaseorder
  WHERE json_value(po_document, '$.User') = 'ABULL';

Note:

When json_value is used, a JSON search index is used as a post-processing filter. The index can be picked up for json_value only if the return value is VARCHAR2, not NUMBER, and only for an equality comparison. For example, it is not picked up for a comparison such as json_value(column, '$.name_first') > 'Nimrod'.

If the name of your JSON search index is present in the execution plan for your query, then you know that the index was in fact picked up for that query. You will see a line similar to that shown in Example 39-34.

Example 39-34 Execution Plan Indication that a JSON Search Index Is Used

|* 2|   DOMAIN INDEX     | PO_SEARCH_IDX |     |     |     4 (0)

A JSON search index is maintained asynchronously, on demand. You can thus defer the cost of index maintenance, performing it at commit time only or at some time when database load is reduced. This can improve DML performance. It can also improve index maintenance performance by enabling bulk loading of unsynchronized index rows when an index is synchronized. On the other hand, asynchronous maintenance of an index means that until it is synchronized the index is not used for data that has been modified or newly inserted.

See Also:

Loading External JSON Data

Example 39-3 and Example 39-4 create table j_purchaseorder and insert a single row of JSON data into it, for illustrative purposes. This section shows how you can create the full table from the data in JSON dump file $ORACLE_HOME/demo/schema/order_entry/PurchaseOrders.dmp. The format of this file is compatible with the export format produced by common NoSQL databases. Each row of the file contains a single JSON document represented as a JSON object.

Example 39-35 creates a database directory that corresponds to file-system directory $ORACLE_HOME/demo/schema/order_entry. Example 39-37 then uses this database directory to create and fill an external table, json_dump_file_contents, with the data from the dump file, PurchaseOrders.dmp.

Example 39-35 Creating a Database Directory Object

CREATE OR REPLACE DIRECTORY order_entry_dir
  AS '$ORACLE_HOME/demo/schema/order_entry';

Example 39-36 Creating a Database Directory Object

CREATE OR REPLACE DIRECTORY loader_output_dir AS '/tmp';Foot 5 

Example 39-37 Creating an External Table and Filling It from a JSON Dump File

CREATE TABLE json_dump_file_contents (json_document CLOB)
  ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_entry_dir
                         ACCESS PARAMETERS
                           (RECORDS DELIMITED BY 0x'0A'
                            DISABLE_DIRECTORY_LINK_CHECK
                            BADFILE loader_output_dir: 'JSONDumpFile.bad'
                            LOGFILE order_entry_dir: 'JSONDumpFile.log'
                            FIELDS (json_document CHAR(5000)))
                         LOCATION (order_entry_dir:'PurchaseOrders.dmp'))
  PARALLEL
  REJECT LIMIT UNLIMITED;

Example 39-38 copies the JSON documents from external table json_dump_file_contents to column json_document ?????? of relational table j_purchaseorder.

Example 39-38 Copying JSON Data from an External Table to a Relational Table

INSERT INTO j_purchaseorder
  SELECT SYS_GUID(), SYSTIMESTAMP, json_document FROM json_dump_file_contents
    WHERE json_document IS JSON;

See Also:

Replicating JSON Data

You can replicate tables with columns containing JSON data using Oracle GoldenGate. Be aware that Oracle GoldenGate requires tables to be replicated to have a nonvirtual primary key column; the primary key column cannot be virtual.

All indexes on the JSON data will be replicated also. However, you must execute, on the replica database, any Oracle Text operations that you use to maintain a JSON search index. Here are examples of such procedures:

  • CTX_DDL.create_section_group

  • CTX_DDL.drop_section_group

  • CTX_DDL.set_sec_grp_attr

  • CTX_DDL.sync_index

  • CTX_DDL.optimize_index

See Also:

Oracle GoldenGate for information about Oracle GoldenGate

Oracle Database Support for JSON

This section describes Oracle Database support for JavaScript Object Notation (JSON).

This support is designed to provide the best fit between the worlds of relational storage and querying JSON data, allowing relational and JSON queries to work well together.

Note:

Oracle is active in standardization efforts regarding SQL access to JSON data as part of a SQL/JSON standard. Oracle Database support for JSON will continue to track the development of such standards and evolve with it.


Footnote Legend

Footnote 1: JSON differs from JavaScript notation in this respect: JSON allows unescaped Unicode characters U+2028 (LINE SEPARATOR) and U+2029 (PARAGRAPH SEPARATOR) in strings. JavaScript notation requires control characters such as these to be escaped in strings. This difference can be important when generating JSONP (JSON with padding) data.
Footnote 2: JavaScript objects are thus similar to hash tables in C and C++, HashMaps in Java, associative arrays in PHP, dictionaries in Python, and hashes in Perl and Ruby.
Footnote 3: An object is created in JavaScript using constructor Object or object literal syntax: {...}.
Footnote 4: If the check constraint combines condition is json with another condition using logical condition OR, then the column is not listed in these views. In this case, it is not certain that data in the column is JSON data. For example, the constraint jcol is json OR length(jcol) < 1000 does not ensure that the data in column jcol is JSON data.
Footnote 5: This example uses a temporary file-system directory. On UNIX and Linux systems this would typically be /tmp. On MS Windows it would typically be folder temp, for example, c:\temp.