The JSON data targeted by a path expression for json_query or a json_table column can be a single JSON value (scalar, object, or array value), or it can be multiple JSON values. With an optional wrapper clause you can wrap the targeted data in an array before returning it.

For example, if the targeted data is the set of values "A50" and {"a": 42} you can specify that those be wrapped to return the array [ "A50", {"a": 42} ] (or [ {"a": 42}, "A50" ] — you cannot control the element order). Or if the only targeted value is 42 then you can wrap that and return the array [42].

Prior to Oracle Database 21c only RFC 4627 was supported, not RFC 8259. A single scalar JSON value could not be returned in this context — wrapping it in an array was necessary, to avoid raising an error. This is still the case if database initialization parameter compatible is less than 20. And even when RFC 8259 is supported you might sometimes want to wrap the result in an array.

The behavior of a wrapper clause (or its absence, which is the same as using keywords WITHOUT WRAPPER) depends on (1) whether or not the targeted JSON data is a single scalar value and (2) whether returning a single scalar value is allowed for the particular invocation of the SQL/JSON function.

Without wrapping, returning a single scalar value or multiple values (scalar or not) raises an error if either of the following is true:

• Database initialization parameter compatible is less than 20.

• Keywords DISALLOW SCALARS are used in the RETURNING clause.

The ON EMPTY clause takes precedence over the wrapper clause. The default for the former is NULL ON EMPTY, which means that if no JSON values match the path expression then SQL NULL is returned. If you want an empty JSON array ([]) returned instead then specify EMPTY ARRAY ON EMPTY. If you want an error raised instead then specify ERROR ON EMPTY.

The wrapper clause for nonempty matches is as follows:

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

• WITHOUT WRAPPER – Use the JSON value or values that match the path expression.

  Raise an error if either of these conditions holds:

  • The path expression matches multiple values.

  • Returning a scalar value is not allowed, and the path expression matches a single scalar value (not an object or array).

• WITH CONDITIONAL WRAPPER – Use a value that represents all of the JSON values that match the path expression.

  If multiple JSON values match then this is the same as WITH WRAPPER.

  If only one JSON value matches:

  • If returning a scalar value is allowed, or if the single matching value is an object or an array, then this is the same as WITHOUT WRAPPER.

  • Otherwise, this is the same as WITH WRAPPER.

The default behavior is WITHOUT WRAPPER.

You can use keyword UNCONDITIONAL if you find that it makes your code clearer: WITH WRAPPER and WITH UNCONDITIONAL WRAPPER mean the same thing.

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

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

Consider, for example, a json_query query to retrieve a JSON object. What happens if the path expression 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 18-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.