Storing and Querying Small JSON Documents

JSON data can be stored in any column that supports character data. If the documents to be stored are small, VARCHAR2 can be used. FIGURE 13.12(A) shows a table, CUSTOMERS1, that consists of a primary key and a single VARCHAR2 type column that stores customer data. To ensure that only well-formed JSON data is stored in the CUSTOMERS1_DATA column, we add an IS JSON constraint for that column.

Data can be inserted with the usual INSERT command. The text data in JSON format is assigned in its native form to the VARCHAR2 column, as shown in FIGURE 13.12(B). Because of the IS JSON constraint, Oracle checks that the data is in valid JSON form.

Data can be retrieved using the SQL SELECT command. The first SELECT statement shown in FIGURE 13.12(C) can be used to retrieve the data, including labels, as a single string. However, because we added the IS JSON constraint, Oracle can treat the data as JSON data, and we can use dot notation to access individual elements of the JSON column, similar to the method used in Chapter 9 for OR data. The second query in Figure 13.12 will return only the zip code. When JSON field names such as ZIP are used with dot notation, it is not necessary to enclose them in double quotes. However, unlike SQL, JSON is case-sensitive so the case must match that used in the JSON document. In this example, we cannot write zip in place of ZIP, or name in place of NAME, as we normally could in Oracle.

Storing and Querying Large JSON Documents

Because the maximum size of VARCHAR2 data is relatively small, large JSON documents can be stored using a LOB type, usually as BLOB, which uses less storage than CLOB and stores the document as is without character-set conversion. FIGURE 13.13(A) shows how to create a CUSTOMER_JSON table with a column, CUSTOMER_DATA, that stores JSON data as BLOB.

Starting with Oracle version 21, the JSON datatype can be used instead of BLOB in the CUSTOMER_DATA column specification. In that case, the IS JSON check is not needed because Oracle will apply the check automatically to validate the JSON input. FIGURE 13.13(B) shows how to specify the JSON datatype.

Inserting JSON data into a column of JSON type is straightforward, requiring no data conversion, and using the same form as that for inserting VARCHAR2 data shown in Figure 13.12(B). To insert data when the BLOB datatype is used, the JSON data must be converted from character to binary before storing, using UTL_RAW.CAST_TO_RAW, as shown in FIGURE 13.13(C), or using Oracle’s TO_BLOB function.

JSON arrays can also be stored in Oracle tables. FIGURE 13.13(D) demonstrates how to insert a nested array of orders within a customer record if the BLOB datatype is used. The same command, without the UTL_RAW.CAST_TO_RAW (), is used if the table was created using the JSON datatype. Note that this customer has two orders, so we have an array of orders, ORDERS, to store them, but each order can have any number of items, so there is a nested array, ITEMS, within each order. Dates in JSON should be written as either "YYYY-MM-DD" or YYYYMMDD, to conform to the ISO 8601 standard, although other formats can be defined.

To insert a large number of records, it is impractical to enter them individually. JSON files can be stored in a directory (usually called a landing area for the data) on the database server machine. The user creates an SQL*Loader control file (with extension .crl), giving the name of the table where the data is to be stored and other details about the file contents, and a second data file (with extension .data) with the names and location of all the JSON files in the landing area that are to be stored in the table, and stores both of these files in the same directory as the JSON files. Finally, the loader is called, passing the names of the control file and the data file as parameters. Details and examples of this process can be found in the online Oracle documentation.

FIGURE 13.14 shows how to retrieve JSON data. Optional conditions can be added to the SELECT command as usual, as shown in Figure 13.14(A). When dot notation is employed, the same SELECT command can be used regardless of whether the data is stored as VARCHAR2, BLOB, or JSON.

The SELECT(*) statement can be used to retrieve all data, including arrays. However, if the data is stored in binary form as a BLOB, the query shown in Figure 13.14(B) will return just a binary string.

Instead, we use the dot notation, which always returns the data as character type. Note that JSON array indexes begin at 0, so to retrieve the first order from this record, we write the query shown in Figure 13.14(C), which finds the order number, name, and item names for the first order for customer 2. Because we did not use an index value for the ITEMS array, both item names are returned.

Figure 13.14(D) demonstrates the use of the wildcard * in an array index to indicate all positions in an array, in this case, the ITEMS array in Customer 2’s first order. Note that except for its use in an array index, [*], a wildcard cannot be used in a path expression.

Figure 13.14(E) demonstrates that several wildcards can be used in the same query. This example displays details of all orders, including all their items, for Customer 2.

Although all the data is retrieved by the query in Figure 13.14(E), the results are displayed in a confusing fashion. To improve the appearance of the results and to add other conditions, we can use the JSON_QUERY function. This function, and a similar one, JSON_VALUE, are useful for complex queries, allowing users to specify the datatype of a single return value using a RETURNING clause, to wrap values as an array using a WITH WRAPPER clause, and to specify actions to be taken when null or empty values are returned or when an error is raised.

The use of JSON_VALUE is illustrated in FIGURE 13.15. Figure 13.15(A) shows the use of JSON_VALUE to return the date of an order as an actual date rather than as a character string. This function takes two arguments. The first is the name of the JSON column in the Oracle table, and the second is a string specifying the single field to be returned and the desired data type of the return. The name after the closing parenthesis is a user-supplied name for the returned value. If none is specified, the name is simply JSON_VALUE.

If the field specified in the SELECT clause does not exist, no error message appears. For the query shown in Figure 13.15(B), which refers to ORDERTIME, a field not included in the data, the result is just the user-supplied name and a blank line, so the user cannot determine whether the field does not exist or there is no data.

The user can add an optional error clause to specify how a runtime error should be handled. Oracle defaults to the condition NULL ON ERROR, returning a null value in such cases. To catch this error, the user can add the condition ERROR ON ERROR as shown in Figure 13.15(C), which provides an appropriate error message.

Although JSON_VALUE is useful for retrieving a single value, the JSON_QUERY function can be used to find an array or object and return the results in a more readable format, called pretty printing. If the query finds more than one value, a wrapper is needed to display the multiple values. FIGURE 13.16 shows the use of JSON_QUERY.

Converting JSON Data to Relational

The JSON_TABLE function can be used to convert JSON results to a relational form, creating a virtual relational table and allowing the user to display the results in a table format or to save them to a relational table. The first argument is the name of the JSON document. This is followed the specification of the columns, with the relational name of each column and the path to the corresponding field in the document. For arrays, a nested path specification, NESTED PATH, is followed by the names and paths for each column of the array. FIGURE 13.17 demonstrates the use of this function.

If the names of the relational columns are the same as those of the attributes, a simpler form can be used, as shown in Figure 13.17(B). In this example, we also show how to insert the virtual table by adding the CREATE TABLE command before the query. The user can then manipulate the relational table using SQL as usual.

Additional Oracle Functions for JSON Data

In addition to JSON_VALUE, JSON_QUERY, and JSON_TABLE, Oracle has the following functions:

• JSON_EXISTS, which tests whether a particular value exists within the given JSON data.

• IS_JSON and IS_NOT_JSON, which test whether the given data is well-formed JSON data.

• JSON_EQUAL, which compares two JSON values and determines whether they are equal, ignoring whitespace and object member order.

• JSON_TEXTCONTAINS, which does a full-text search of JSON data to determine whether it contains a particular expression. It is used in a WHERE clause or in a CASE statement.

Oracle also permits users to develop a JSON data guide, a facility that allows them to discover information about stored JSON documents, including their type, structure, and contents. A data guide can be updated automatically as new JSON documents are added to the database.

Updating JSON Using SQL

FIGURE 13.18 shows how to update JSON data. To update a value in a JSON column in an Oracle database for a version earlier than 19c, it is necessary to replace the entire record, as shown in Figure 13.18(A), which updates Customer 2’s address.

Oracle 19c and later versions provide a method of patching, called JSON_MERGEPATCH, that allows users to specify only the fields to be updated, as shown in Figure 13.18(B). Although scalar values can be updated by a patch, updating an array still requires replacing the entire array in Oracle 19c.

Oracle 21c, which introduced a native JSON datatype, simplifies updates, including array updates, with the JSON_TRANSFORM function. This function allows the user to insert new values, replace existing values, and remove values in a single statement. Details can be found online in the Oracle JSON Developer’s Guide.

Creating Relational Tables with XML Columns

A relational database can have any number of columns of XMLTYPE alongside columns of any other data type that Oracle supports. FIGURE 13.19 shows how to create and populate such a table. FIGURE 13.19(A) shows an SQL command to create a simple table containing a numeric CUSTID column and an XMLTYPE column called CONTACTINFO.

Records can be inserted in several ways, such as reading from a file or using an API. Inserting records individually is easily done through SQL*Plus, using XMLTYPE (or XMLTYPE.CREATEXML) in the same way that we used constructors in OO and OR databases, as shown in FIGURE 13.19(B). The XML input data is formatted exactly as shown earlier in Figure 13.2, with each element tagged. For this example, the other two records shown in Figure 13.2 are inserted in the same manner.

Querying XML Data

There are several ways users can query and manipulate XML data in Oracle XML DB. One of the simplest methods is by using the XQuery language in SQL*Plus, although it can also be used in PL/SQL or Java. The main functions are XMLQuery, XMLCast, XMLTable, and XMLExists. XMLQuery is generally used in the SELECT clause, XMLTable in the FROM clause, and XMLExists in the WHERE clause of queries. The general form of an XMLQuery statement using a path expression is

Here, <XQuery string> is a standard XQuery language string containing a path or FLWOR expression as described in Section 13.4.4, and <xmltype column> is the table column to be returned. You can add NULL ON EMPTY before the closing parenthesis to catch errors if no data is found, and you can add a column name for the return after the closing parenthesis. FIGURE 13.20 shows several queries using XQuery. FIGURE 13.20(A) shows a query that finds the names of all customers.

Conditions can be added as described in Section 13.4.4. The query shown in FIGURE 13.20(B) finds the telephone numbers of all inactive customers. The first example uses a path expression, while the second shows the use of a FLWOR expression.

These examples return XML data with tags surrounding the results. The XMLCAST function can be used to change XML data into a scalar. Its form is

The scalar datatypes that can follow the word AS are NUMBER, VARCHAR2, CHAR, CLOB, BLOB, REFXMLTYPE, and any SQL date or time type. FIGURE 13.20(C) demonstrates the use of the XMLCAST function to change the query in Figure 13.20(A) to character data.

The XMLTable function is used to transform the results of an XQuery expression into a virtual relational table, which can then be stored as a regular or temporary table, and operated on as a normal relational table using SQL. It appears in the FROM clause. The format is

FIGURE 13.20(D) shows its use to create a virtual relational table consisting of columns CUSTNAME, CUSTADDRESS, and CUSTPHONE for active customers. Note that the source table (here, CUSTOMERS) must be named in the FROM clause, as shown.

Using XMLTABLE, the user is essentially shredding the XML document, dividing it into components that are mapped to columns of tables. The table created can be given a name and then treated as a regular relational table by writing a CREATE TABLE... AS command just before the previous query, as in

The XMLExists function tests whether an XQuery expression returns a non-empty string, that is, it returns true if the value is found, and false otherwise. It is used in the WHERE clause (or a CASE statement) and its form is

An example is shown in FIGURE 13.20(E), which returns the CUSTID of any customer whose area code is 212.

Object Tables of XMLTYPE

Oracle also supports object tables that consist entirely of native XMLTYPE entries, as shown in FIGURE 13.21. XML object tables can be created as illustrated in Figure 13.21(A). Like all object tables, no other columns are specified when such a table is created; all of its entries are instances of XML documents. Although the usual method of populating such tables is by reading directly from an XML document in a file, it is possible to insert XML records individually using SQL*Plus, as shown in Figure 13.21(B). The two other customer records can be entered in the same way.

Because a table of XMLType is an object table, we can use the SELECT OBJECT_VALUE or simply SELECT VALUE() as shown in Figure 13.21(C) (as we did in Chapter 9 for Oracle OR tables).

We can also use the same XQuery functions XMLQuery, XMLTable, and XMLExists that we used for tables with object columns, so the queries shown in Figure 13.19 run with slight modifications for the CUSTOMERS_XML table. The modified queries are shown in Figure 13.21(D). The reference to CUSTID is dropped, and OBJECT_VALUE is used in the PASSING clause.

Updating XML Data

Starting with Oracle 12, Oracle XML DB follows the W3 recommendation by providing an XQuery Update function, which replaces the UPDATEXML function that was used in earlier versions. The UPDATE statement can be used to update an entire XML document or selected nodes of the document, as shown in FIGURE 13.22. The right side of the SET line must be an XMLType instance.

The example shown in FIGURE 13.22(A) changes a customer’s name and telephone number in a single statement, while replacing the entire document (the customer’s record). Note that the condition is expressed using the XQuery function XMLExists.

To update the value of a node instead of the entire document, we can modify the contents of the node as shown in FIGURE 13.22(B), which changes the name of customer Mary Jones to Mary King.

In addition to updating existing nodes, new nodes can be inserted or old ones deleted. In FIGURE 13.22(C) we insert a second telephone number for the WorldWide Travel Agency.

In Figure 13.22 we illustrate removing a node by deleting the STATE in the address of the WorldWide Travel Agency.

Obtaining XML Data from a File

In the previous examples, we input the XML data from SQL*Plus one record at a time. Data can also be read directly from a file. For example, to read the data shown in the XML instance document in Figure 13.4, you first store the document in an operating system directory on the database server machine. For example, if you have created a directory on the C: drive called DBBook, and saved the XML file in that directory using the name CustomerList.xml, you can enter data from the file into an Oracle table by executing the commands shown in FIGURE 13.23 using SQL*Plus.

Oracle shows that one record has been inserted. If you retrieve the data using the command

the data is retrieved as a single record, which is the entire CustomerList. However, you can retrieve individual customer records by modifying the queries shown in Figure 13.21(D) to include CUSTOMERLIST as part of the path. For example, the first query is modified as shown in FIGURE 13.23(B).

The other queries in Figure 13.21(D) are modified in the same way. Data can be retrieved using XQuery as shown earlier and stored in a relational table using XMLTable, which is essentially shredding the XML document.

When an XML schema exists, data can be entered from an XML instance document directly to an Oracle table and shredded automatically. This process requires registering the XML schema for the document. Registration permits XML DB to create an OR database structure that best fits the XML schema, matching up data types for elements and attributes, creating structures for objects, and using VARRAYs or nested tables for repeating elements. XML instance documents used as input can then be shredded automatically and their components stored in the OR database. The registration process and the XML schema creation can be done in a single step through PL/SQL using the registerSchema method, which has several parameters. The main parameters are the URL where the schema is located, the schema document, the encoding method used, and options for how the schema should be registered. Once a schema is registered, Oracle can do a mapping to an OR table, unless the programmer has chosen other options. The document root corresponds to the name of the table, and elements and attributes are mapped to table columns. VARRAYs and nested tables can store complex data. Shredding the documents provides better performance, but queries can be written as shown previously.