![]() ![]() This is the default.ĮRROR ON ERROR - Returns the appropriate Oracle error when an error occurs.ĮMPTY ON ERROR - Specifying this clause is equivalent to specifying EMPTY ARRAY ON ERROR.ĮMPTY ARRAY ON ERROR - Returns an empty JSON array ( ) when an error occurs. NULL ON ERROR - Returns null when an error occurs. The function matches a single scalar value or, multiple values of any type, and the WITH WRAPPER clause is not specified The return value data type is not large enough to hold the return character string You can override the behavior for this type of error by specifying the JSON_query_on_empty_clause. No match is found when the JSON data is evaluated using the SQL/JSON path expression. Use this clause to specify the value returned by this function when the following errors occur:Įxpr is not well-formed JSON data using strict or lax JSON syntax You can use the JSON_query_on_error_clause to override this default behavior. If the function returns a single scalar value, or multiple values of any type, and you do not specify WITH WRAPPER, then the function returns null by default. The ARRAY keyword is optional and is provided for semantic clarity. If the path expression matches a single JSON object or JSON array, then the array wrapper is omitted. Specify WITH CONDITIONAL WRAPPER to include the array wrapper only if the path expression matches a single scalar value or multiple values of any type. The UNCONDITIONAL keyword is provided for semantic clarity. As you can see for row 3 (Jill) the status Gold occurs inside an. JSONVALUE can only select and return scalar. JSON ‘null’ from a missing value in JSONVALUE. Since JSON can have a ‘null’ value one could not differentiate a. Specifying the WITH UNCONDITIONAL WRAPPER clause is equivalent to specifying the WITH WRAPPER clause. There are a couple of reasons: JSONEXISTS checks for the existence of a. You must specify this clause if the path expression matches a single scalar value (a value that is not a JSON object or JSON array) or multiple values of any type. Specify WITH WRAPPER to include the array wrapper. You can specify this clause only if the path expression matches a single JSON object or JSON array. Specify WITHOUT WRAPPER to omit the array wrapper. type.Use this clause to control whether this function wraps the values matched by the path expression in an array wrapper-that is, encloses the sequence of values in square brackets ( ). Because of SQL/JSON path-expression syntax relaxation. Note that if path expression $. were used in Example 16-1 it would give the same result. For the document in Example 4-2 the phone types are "Office" and "Mobile", and the array returned is either or. So to create a table that will store 'large' JSON documents, run: Copy code snippet. ![]() For each document it returns a VARCHAR2 value whose contents represent a JSON array with elements the phone types, in an unspecified order. The error clause for json_query can specify EMPTY ON ERROR, which means that an empty array ( ) is returned in case of error (no error is raised).Įxample 16-1 shows an example of the use of SQL/JSON function json_query with an array wrapper. The wrapper clause determines the form of the returned string value. In the RETURNING clause you can specify only data type VARCHAR2 you cannot specify NUMBER. The path expression can target any number of JSON values. The second argument to json_query is a SQL/JSON path expression followed by optional clauses RETURNING, WRAPPER, ON ERROR, and ON EMPTY. The result of evaluating the SQL expression is used as the context item for evaluating the path expression. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. It can be of data type VARCHAR2, BLOB, or CLOB. The first argument to json_query is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). ![]()
0 Comments
Leave a Reply. |