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.

BigQueryDb2 (LUW)aMariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitelistagg(distinct …)
  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

BigQueryDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitejson_object(*)json_object(<udt>)json_array(<udt>)json_value(…RETURNING <udt>)

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.

Support My Work

I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.

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.

BigQueryDb2 (LUW)MariaDBaMySQLaOracle DBbPostgreSQLSQL ServerSQLitenested … for json_table(…)json_serialize(<j>)json_mergepatch(<j>, <p>)
  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 (2016 and 2023) 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
BigQueryDb2 (LUW)MariaDBMySQLOracle DBaaaaPostgreSQLSQL ServerSQLite.double().ceiling().floor().abs().size() on JSON object? (…) in json_exists()? (…) in json_value()? (…) in json_query()? (…) in json_table()?(…) followed by further steps
  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.

BigQueryDb2 (LUW)MariaDBMySQLOracle DBaaPostgreSQLSQL ServerSQLitedescendant accessor ..<key>.length().lower().upper()
  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.

You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-⁠sql.com on your radar.

About the Author

Photo of Markus Winand

Markus Winand provides insights into SQL and shows how different systems support it at modern-sql.com. Previously he made use-the-index-luke.com, which is still actively maintained. Markus can be hired as trainer, speaker and consultant via winand.at.

Buy the Book

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy now!
(paperback and/or PDF)

Paperback also available at Amazon.com.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »

Footnotes

  1. Json_object(*): ISO/IEC 9075-2:2023 §6.34 BNF for <JSON object constructor>

    Json_object(<udt>) and json_array(<udt>): ISO/IEC 9075-2:2023 §6.13 SR 7 (no cast for JSON)

    Json_value(…returning <udt>): ISO/IEC 9075-2:2023 §6.28 SR 2

  2. Not even the examples shown in Appendix A “Example Test Cases” of the RFC work properly.

  3. ISO/IEC 9075-2:2023 §9.49 GR 11gii6CII2a

Connect with Markus Winand

Subscribe mailinglistsSubscribe the RSS feedMarkus Winand on LinkedInMarkus Winand on XINGMarkus Winand on TwitterMarkus Winand on Bluesky
Copyright 2015-2024 Markus Winand. All righs reserved.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR