What’s new in Oracle Database Release 19c

The first on-premises download of the Oracle Database 19c became available in April 2019. In the old numbering scheme, this release would just be a patch release, so it is no surprise that this release is “focused on fixing known issues, rather than adding new functionality”.

There are nevertheless some enhancements in the supported SQL dialect and other noteworthy changes.

Contents:

1. Listagg(distinct …)
2. Extensions to Existing JSON Functions
3. New JSON Functions
4. Standard SQL/JSON Path Features
5. SQL/JSON Path Extensions
6. Desupported Features
7. Oracle Documentation

Listagg(distinct …)

Let’s start with the simple things: the listagg function now accepts a distinct specification as described by the standard. This is a convenient way to remove duplicate values when turning rows into a (comma separated) string.

1. If ordered by the aggregated values: listagg(distinct X,…) within group (order by X)

Extensions to Existing JSON Functions

Most new SQL features in version 19c are related to JSON. The first type of these JSON extensions are proprietary extensions to SQL standard functions that were already supported in earlier releases.

The first is about the json_object function: it now accepts expressions with an asterisk (*) similar to the select clause. Json_object(*) will thus create a JSON object with one attribute for each column. It is also possible to qualify the asterisk with a table name and list additional columns: json_object(t1.*, t2.column). See: Documentation.

The second extensions provides a mapping between user-defined SQL object types (create type <udt>) and JSON objects. For that, json_object and json_array accept user defined types as arguments and create JSON objects with all attributes of that type. The reverse mapping is provided by the json_value function, which now accepts user-defined types in the returning clause. See: Documentation.

As the standard doesn’t describe these functions, they are marked grey in the following matrix.0

New JSON Functions

Release 19c introduced new JSON functions and an abbreviated syntax for the json_table function. As I find the shortened json_table syntax to be harming to the clarity of the statement, I’ll just point you toward the documentation instead of showing an example.

The new function json_serialize is, according to the documentation, useful for converting binary documents into its JSON text representation. However, this should also be possible with json_query. A notable difference is that json_serialize accepts additional parameters to control the output format. In particular, pretty can be used to get properly indented JSON.

The most interesting new function is json_mergepatch. It allows you to add new attributes to existing JSON objects and to replace or delete existing attributes. The documentation states that it implements RFC 7396, although there are some gaps when looking into the details.1 It is also surprising that the other SQL database from Oracle—MySQL—uses a slightly different name for this functionality: json_merge_patch.

The json_mergepatch function expects two JSON arguments: first the document that should be changed, then the patch document that describes the changes.

JSON_MERGEPATCH('{"a": "unchanged",
                  "b": "overwrite",
                  "c": "remove"}'
               ,
                '{"b": "overwritten",
                  "c":  null,
                  "d": "new"}'
               )

It returns the changed JSON document. Attributes that are present in the patch document will be deleted first if their value in the patch document is the JSON null value. Otherwise the result will have those attributes with the values from the patch document. The result of this example is therefore as follows (although without formatting):

{"a": "unchanged",
 "b": "overwritten",
 "d": "new"}

Json_mergepatch is not limited to flat objects like those shown in this example. It can also operate on nested structures and remove or replace entire objects and arrays. It cannot, however, operate on individual elements of an array.

1. Use json_merge_patch instead (note the second underscore)
2. Improper handling of some non-JSON arguments

Standard SQL/JSON Path Features

SQL/JSON path is used by some SQL/JSON function to access parts of a JSON document—similar to XPath for XML or CSS selectors for HTML.

The SQL/JSON path functionality defined in the SQL standard is still not fully supported by Oracle Database. However, version 19c has closed some gaps.

Item Methods

So-called item methods are SQL/JSON path functions that can be applied to JSON elements identified by an SQL/JSON path expression. Starting with version 19c, the Oracle database also supports the functions .abs(), .ceiling(), and .floor()—but only in filter expressions (see below).

The .double() function is a special case here. It used to work in version 12.2, but was broken in 18c. It seems to work again in 19c.

The item method .size() has also undergone an odd change. The SQL standard of 2016 says that this function returns the number of elements when applied on a JSON array. When using this method on another JSON type, it returns 1.2 Oracle Database 18c actually behaved like that. In version 19c, .size() applied to a JSON object doesn’t return 1 anymore, rather it gives the number of attributes in the object. Although that might be a useful behavior, it is a different behavior than described by the standard. This behavior is therefore marked with a red X in the support matrix.

Filter Expressions

SQL/JSON path filter expressions can keep or drop JSON elements based on a condition—similar to XPath predicates ([…]). Filter expressions are introduced by a question mark followed by the condition in parenthesis: ? (…).

Astonishingly, prior to version 19c the Oracle database only supported filter predicates in the json_exists function. Using them in other functions such as json_query resulted in “ORA-40553: path expression with predicates not supported in this operation”. This was pretty surprising, as the SQL standard doesn’t define different SQL/JSON path dialects per function. Version 19c now accepts filter expressions in all JSON functions.

Another limitation of filter expression that was lifted with version 19c is that they could only be used as the last step of an SQL/JSON path expression. The following example shows an expression that resulted in a syntax error prior to version 19c because the filter expression ? (@.a > 42) is followed by another step (.b).

$ ? (@.a > 42) .b

1. Only in filter expressions (? (…))

SQL/JSON Path Extensions

Oracle Database 19c also introduced SQL/JSON path functionality that is not covered by the SQL standard.

The first addition in this area is a member accessor that recursively descends into nested JSON objects. The syntax is similar to the normal attribute accessor—it just uses two leading periods: ..<key>.

Furthermore, version 19c introduced proprietary item methods that operate on character strings: .length(), .lower(), .upper(). As of version 19c they can be used in filter expressions only.

1. Only in filter expressions (? (…))

Desupported Features

The change that caused the most discussions about version 19c was not a new feature, but the removal of an old one. Starting with version 19c, the high availability solution RAC is not available for the standard edition SE2 anymore. If you remember that this version is supposed to be a patch release, it is a rather surprising move. Users of SE2 RAC installations will be facing an unfortunate choice sooner or later: either license the more expensive enterprise edition or use another high availability solution.

Oracle Documentation

Or course version 19c has also brought changes to other areas than the SQL dialect. Please refer to the Oracle documentation for an exhaustive list.