listagg(distinct X,…) within group (order by X)
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
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.
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.
json_merge_patch
instead (note the second underscore)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.
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.
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
? (…)
)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.
? (…)
)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.
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.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »
Json_object(*)
: SQL:2016-2: §6.33 Format for <JSON object constructor>
Json_object(<udt>)
and json_array(<udt>)
: SQL:2016-2: §6.13 SR12 (no cast
between user-defined types and character strings—if there was a distinct SQL/JSON type, that might change)
Json_value(…returning <udt>)
: SQL:2016-2: §6.27 SR2
Not even the examples shown in Appendix A “Example Test Cases” of the RFC work properly.
SQL:2016-2: §9.39 GR 11 g ii 6 C II 2 a