Oracle создать json примеры
This article gives basic examples of the SQL/JSON generation functions introduced in Oracle Database 12c Release 2 (12.2). Remember, there were SQL/JSON functions and conditions added in Oracle Database 12c Release 1 (12.1) also, as described here.
FORMAT JSON Clause
The FORMAT JSON clause is optional and is provided for "semantic clarity". For the most part Oracle understands if data is in JSON format, so this clause is redundant, but if you are supplying JSON in the form of a BLOB you must use the FORMAT JSON clause. Using it does seem to have an impact on how the JSON output is quoted.
23.1 Overview of SQL/JSON Generation Functions
You can use SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
These generation functions make it easy to construct JSON data directly from a SQL query. They allow non-JSON data to be represented as JSON objects and JSON arrays. You can generate complex, hierarchical JSON documents by nesting calls to these functions. Nested subqueries can generate JSON collections that represent one-to-many relationships. Foot 1
The Best Way to Construct JSON Data from Non-JSON Data
Alternatives to using the SQL/JSON generation functions are generally error prone or inefficient.
Using string concatenation to generate JSON documents is error prone. In particular, there are a number of complex rules that must be respected concerning when and how to escape special characters, such as double quotation marks ( " ). It is easy to overlook or misunderstand these rules, which can result in generating incorrect JSON data.
Reading non-JSON result sets from the database and using client-side application code to generate JSON data is typically quite inefficient, particularly due to network overhead. When representing one-to-many relationships as JSON data, multiple SELECT operations are often required, to collect all of the non-JSON data needed. If the documents to be generated represent multiple levels of one-to-many relationships then this technique can be quite costly.
The SQL/JSON generation functions do not suffer from such problems; they are designed for the job of constructing JSON data from non-JSON database data.
They always construct well-formed JSON documents.
By using SQL subqueries with these functions, you can generate an entire set of JSON documents using a single SQL statement, which allows the generation operation to be optimized.
Because only the generated documents are returned to a client, network overhead is minimized: there is at most one round trip per document generated.
The SQL/JSON Generation Functions
Functions json_object and json_array construct a JSON object or array, respectively. In the simplest case, json_object takes SQL name–value pairs as arguments, and json_array takes SQL values as arguments.
Functions json_objectagg , and json_arrayagg are aggregate SQL functions. They transform information that is contained in the rows of a grouped SQL query into JSON objects and arrays, respectively. Evaluation of the arguments determines the number of object members and array elements, respectively; that is, the size of the result reflects the current queried data.
For json_objectagg and json_arrayagg , the order of object members and array elements, respectively, is unspecified. For json_arrayagg , you can use an ORDER BY clause within the json_arrayagg invocation to control the array element order.
Result Returned by SQL/JSON Generation Functions
By default, the generated JSON data is returned from a generation function as a SQL VARCHAR2 value. You can use the optional RETURNING clause to specify a VARCHAR2 size or to specify a CLOB or BLOB return value instead. When BLOB is the return type, the character set is AL32UTF8.
Handling of Input Values For SQL/JSON Generation Functions
The SQL/JSON generation functions take SQL values as input and render them as JSON values inside the SQL value that is returned. How the input values are rendered as JSON depends on their SQL data type.
Optional Behavior For SQL/JSON Generation Functions
You can optionally specify a SQL NULL -handling clause, a RETURNING clause, and keyword STRICT .
NULL -handling clause — Determines how a SQL NULL value resulting from input evaluation is handled.
NULL ON NULL — An input SQL NULL value is converted to JSON null for output. This is the default behavior for json_object and json_objectagg .
ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_array and json_arrayagg .
RETURNING clause — The SQL data type used for the function return value. The default is VARCHAR2(4000) .
STRICT keyword — If present, the returned JSON data is checked, to be sure it is well-formed. If STRICT is present and the returned data is not well-formed then an error is raised.
Related Topics
Oracle Database SQL Language Reference for information about SQL/JSON function json_array
Oracle Database SQL Language Reference for information about SQL/JSON function json_arrayagg
Oracle Database SQL Language Reference for information about SQL/JSON function json_object
Oracle Database SQL Language Reference for information about SQL/JSON function json_objectagg
JSON_OBJECTAGG
The JSON_OBJECTAGG aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.
23.2 Handling of Input Values For SQL/JSON Generation Functions
The SQL/JSON generation functions take SQL values as input and return a JSON object or array. The input values are used to produce JSON object field–value pairs or JSON array elements. How the input values are used depends on their SQL data type.
The returned JSON object or array is of a SQL data type that supports JSON data: JSON , VARCHAR2 , CLOB , or BLOB . The default return type is VARCHAR2(4000) . In all cases, the return value is known by the database to contain well-formed JSON data.
Unless it is of JSON data type, an input can optionally be followed by keywords FORMAT JSON , which declares that the value is to be considered as already representing JSON data (you vouch for it), so it is interpreted (parsed) as JSON data. For example, if the input is '<>' then you might want it to produce an empty JSON object , <> , and not a JSON string , "<>" . Example 23-1 illustrates the use of FORMAT JSON to cause input SQL string "true" to produce the JSON Boolean value true .
Equivalently, if the input type is not JSON then you can apply SQL function treat with keywords AS JSON to it — the effect is the same as using FORMAT JSON .
If the input data is of JSON type then it is used as is. This includes the case where the JSON type constructor is used. (Do not use FORMAT JSON or treat … AS JSON in this case; otherwise, an error is raised.)
In some cases where an input is not of JSON type, and you do not use FORMAT JSON or treat … AS JSON , Oracle nevertheless knows that the result is JSON data. In such cases using FORMAT JSON or treat … AS JSON is not needed and is optional. This is the case, for example, if the input data is the result of using function json_query or one of the JSON generation functions.
If, one way or another, an input is known to be JSON data then it is used essentially as is to construct the result — it need not be processed in any way. This applies regardless of whether the input represents a JSON scalar, object, or array.
If an input is not known to be JSON data, then it produces a JSON value as follows (any other SQL value raises an error):
An instance of a user-defined SQL object type produces a JSON object whose field names are taken from the object attribute names and whose field values are taken from the object attribute values (to which JSON generation is applied recursively).
An instance of a SQL collection type produces a JSON array whose element values are taken from the collection element values (to which JSON generation is applied recursively).
A VARCHAR2 , CLOB , or NVARCHAR value is wrapped in double quotation marks ( " ), and characters are escaped when necessary to conform to the JSON standard for a JSON string . For example, input SQL input '<>' produces the JSON string "<>" .
A numeric value produces a JSON numeric value.
If compatible is at least 20 then NUMBER input produces a JSON number value, BINARY_DOUBLE input produces a JSON double value, and BINARY_FLOAT input produces a JSON float value.
If database initialization parameter compatible is less than 20 then the value is a JSON number, regardless of the numeric input type ( NUMBER , BINARY_DOUBLE , or BINARY_FLOAT ).
The numeric values of positive and negative infinity, and values that are the undefined result of a numeric operation ("not a number" or NaN ), cannot be expressed as JSON numbers. They instead produce the JSON strings "Inf" , "-Inf" , and "Nan" , respectively.
A RAW or BLOB value produces a hexadecimal JSON string, with double quotation marks, ( " ).
A time-related value ( DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , INTERVAL YEAR TO MONTH , or INTERVAL DAY TO SECOND ) produces a supported ISO 8601 format, and the result is enclosed in double quotation marks ( " ) as a JSON string.
A BOOLEAN PL/SQL value of TRUE or FALSE produces JSON true or false , respectively.
A SQL NULL value produces JSON null , regardless of the NULL data type.
For input of data types CLOB and BLOB , an empty instance is distinguished from SQL NULL . It produces an empty JSON string ( "" ). But for input of data types VARCHAR2 , NVARCHAR2 , and RAW , Oracle SQL treats an empty (zero-length) value as NULL , so do not expect such a value to produce a JSON string.
Example 23-1 Declaring an Input Value To Be JSON
This example specifies FORMAT JSON for SQL string values 'true' and 'false' , in order that the JSON Boolean values true and false are used. Without specifying FORMAT JSON , the values of field hasCommission would be the JSON string values "true" and "false" , not the JSON Boolean values true and false .
SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg are presented.
You can use SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
SQL/JSON function json_object constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier . Each value of a pair can be any SQL expression . The name and value are separated by keyword VALUE .
SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions. Each argument can be any SQL expression. Array element order is the same as the argument order.
SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.
Handling NULLs
All of the SQL/JSON functions have the ability determine how null values are handled. The default is NULL ON NULL , but this can be altered to ABSENT ON NULL .
Setup
The examples in this article use the following tables.
19.1 Overview of SQL/JSON Generation Functions
You can use SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
These generation functions make it easy to construct JSON data directly from a SQL query. They allow non-JSON data to be represented as JSON objects and JSON arrays. You can generate complex, hierarchical JSON documents by nesting calls to these functions. Nested subqueries can generate JSON collections that represent one-to-many relationships. Foot 1
The Best Way to Construct JSON Data from Non-JSON Data
Alternatives to using the SQL/JSON generation functions are generally error prone or inefficient.
Using string concatenation to generate JSON documents is error prone. In particular, there are a number of complex rules that must be respected concerning when and how to escape special characters, such as double quotation marks ( " ). It is easy to overlook or misunderstand these rules, which can result in generating incorrect JSON data.
Reading non-JSON result sets from the database and using client-side application code to generate JSON data is typically quite inefficient, particularly due to network overhead. When representing one-to-many relationships as JSON data, multiple SELECT operations are often required, to collect all of the non-JSON data needed. If the documents to be generated represent multiple levels of one-to-many relationships then this technique can be quite costly.
The SQL/JSON generation functions do not suffer from such problems; they are designed for the job of constructing JSON data from non-JSON database data.
They always construct well-formed JSON documents.
By using SQL subqueries with these functions, you can generate an entire set of JSON documents using a single SQL statement, which allows the generation operation to be optimized.
Because only the generated documents are returned to a client, network overhead is minimized: there is at most one round trip per document generated.
The SQL/JSON Generation Functions
Functions json_object and json_array construct a JSON object or array, respectively, given as arguments SQL name–value pairs and values, respectively. The number of arguments corresponds to the number of object members and array elements, respectively (except when an argument expression evaluates to SQL NULL and the ABSENT ON NULL clause applies).
Each name must have the syntax of a SQL identifier. Each value can be any SQL value, including a value computed using a scalar SQL (sub)query that returns at most one item (a single row with a single column — an error is raised if such a query argument returns more than one row.)
Functions json_objectagg , and json_arrayagg are aggregate SQL functions. They transform information that is contained in the rows of a grouped SQL query into JSON objects and arrays, respectively. Evaluation of the arguments determines the number of object members and array elements, respectively; that is, the size of the result reflects the current queried data.
For json_objectagg and json_arrayagg , the order of object members and array elements, respectively, is unspecified. For json_arrayagg , you can use an ORDER BY clause within the json_arrayagg invocation to control the array element order.
Result Returned by SQL/JSON Generation Functions
By default, the generated JSON data is returned from a generation function as a SQL VARCHAR2 value. You can use the optional RETURNING clause to specify a VARCHAR2 size or to specify a CLOB or BLOB return value instead. When BLOB is the return type, the character set is AL32UTF8.
Handling of Input Values For SQL/JSON Generation Functions
The generation functions take SQL values as input and render them as JSON values inside the SQL value that is returned. How the input values are rendered as JSON depends on their SQL data type. By default, a SQL NUMBER value is rendered in the output as a JSON number, a SQL VARCHAR2 value is rendered as a JSON string, and so on. For example, by default the VARCHAR2 value '<>' is rendered as the JSON string "<>" .
In some cases you know or expect that an input value in fact already represents JSON data, and you want to communicate this to the generation function so that the value is kept as is. For example, if the input is '<>' then you might want it interpreted (rendered) as an empty JSON object , <> , not as a JSON string , "<>" .
You can do this by adding keywords FORMAT JSON after an input expression to declare that the value that results from it is to be considered as already representing JSON data. Equivalently, you can apply SQL function treat with keywords AS JSON to a generation-function input value — the effect is the same as using FORMAT JSON .
In many cases Oracle can determine automatically that an input value is in fact JSON data, in which case the input is treated as if it were followed by an explicit FORMAT JSON declaration. This is the case, for instance, if the value expression is itself an invocation of a SQL/JSON generation function or function json_query .
If you do not specify FORMAT JSON for a given input value, and if Oracle cannot determine that the value is JSON data, then it is assumed to be ordinary (non-JSON) SQL data. In that case it is serialized as follows (any other SQL value raises an error):
A VARCHAR2 , CLOB , or NVARCHAR value is wrapped in double quotation marks ( " ), and characters are escaped when necessary to conform to the JSON standard.
A numeric value ( NUMBER , BINARY_DOUBLE , or BINARY_FLOAT ) is rendered as a JSON number. (It is not quoted.)
A RAW or BLOB value is rendered as a hexadecimal JSON string (with double quotation marks, " ).
A time-related value ( DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , INTERVAL YEAR TO MONTH , or INTERVAL DAY TO SECOND ) is rendered in a supported ISO 8601 format, and the result is enclosed in double quotation marks ( " ).
A BOOLEAN PL/SQL value is rendered as JSON true or false . (It is not quoted.)
A NULL value is rendered as JSON null , regardless of the NULL data type. (It is not quoted.)
For data types CLOB and BLOB , an empty instance is distinguished from NULL and is rendered as an empty JSON string ( "" ). But for data types VARCHAR2 , NVARCHAR2 , and RAW , Oracle SQL treats an empty (zero-length) value as NULL , so do not expect such a value to be rendered as a JSON string.
The format of an input argument can affect the format of the data that is returned by the function. In particular, if an input is declared or automatically determined to be of format JSON then it is treated as JSON data when computing the return value. Example 19-1 illustrates this — it explicitly uses FORMAT JSON to interpret the SQL string "true" as the JSON Boolean value true .
Optional Behavior For SQL/JSON Generation Functions
You can optionally specify a SQL NULL -handling clause, a RETURNING clause, and keyword STRICT .
NULL -handling clause — Determines how a SQL NULL value resulting from input evaluation is handled.
NULL ON NULL — An input SQL NULL value is converted to JSON null for output. This is the default behavior for json_object and json_objectagg .
ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_array and json_arrayagg .
RETURNING clause — The SQL data type used for the function return value. The default is VARCHAR2(4000) .
STRICT keyword — If present, the returned JSON data is checked, to be sure it is well-formed. If STRICT is present and the returned data is not well-formed then an error is raised.
Oracle Database SQL Language Reference for information about SQL/JSON function json_array
Oracle Database SQL Language Reference for information about SQL/JSON function json_arrayagg
Oracle Database SQL Language Reference for information about SQL/JSON function json_object
Oracle Database SQL Language Reference for information about SQL/JSON function json_objectagg
Example 19-1 Declaring an Input Value To Be JSON
This example specifies FORMAT JSON for SQL string values 'true' and 'false' , in order that the JSON Boolean values true and false are used. Without specifying FORMAT JSON , the values of field hasCommission would be the JSON string values "true" and "false" , not the JSON Boolean values true and false .
Related Topics
19.2 JSON_OBJECT SQL/JSON Function
SQL/JSON function json_object constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier . Each value of a pair can be any SQL expression . The name and value are separated by keyword VALUE .
The evaluated arguments you provide to json_object are explicit object field names and field values. The resulting object has an member for each pair of name–value arguments you provide (except when an value expression evaluates to SQL NULL and the ABSENT ON NULL clause applies).
Example 19-2 Using JSON_OBJECT to Construct JSON Objects
This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR ) whose salary is less than 15000. The object includes, as the value of its field contactInfo , an object with fields mail and phone .
Because the return value of json_object is JSON data, FORMAT JSON is deduced for the input format of field contactInfo — the explicit FORMAT JSON here is not needed.
Example 19-3 Using JSON_OBJECT With ABSENT ON NULL
This example queries table hr.locations from standard database schema HR to create JSON objects with fields city and province .
The default NULL -handling behavior for json_object is NULL ON NULL .
In order to prevent the creation of a field with a null JSON value, the example uses ABSENT ON NULL . The NULL SQL value for column state_province when column city has value 'Singapore' means that no province field is created for that location.
SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg are presented.
You can use SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
The SQL/JSON generation functions take SQL values as input and render them as JSON values inside the SQL value that is returned. How the input values are rendered as JSON depends on their SQL data type.
SQL/JSON function json_object constructs JSON objects from the results of evaluating its argument SQL expressions.
SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions.
SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.
JSON_ARRAY
The JSON_ARRAY function converts a comma-separated list of expressions into a JSON array of JSON values.
23.2 Handling of Input Values For SQL/JSON Generation Functions
The SQL/JSON generation functions take SQL values as input and render them as JSON values inside the SQL value that is returned. How the input values are rendered as JSON depends on their SQL data type.
By default, a SQL NUMBER value is rendered in the output as a JSON number, a SQL VARCHAR2 value is rendered as a JSON string, and so on. For example, by default the VARCHAR2 value '<>' is rendered as the JSON string "<>" .
In some cases you know or expect that an input value in fact already represents JSON data, and you want to communicate this to the generation function so that the value is kept as is. For example, if the input is '<>' then you might want it interpreted (rendered) as an empty JSON object , <> , not as a JSON string , "<>" .
You can do this by adding keywords FORMAT JSON after an input expression to declare that the value that results from it is to be considered as already representing JSON data. Equivalently, you can apply SQL function treat with keywords AS JSON to a generation-function input value — the effect is the same as using FORMAT JSON .
In many cases Oracle can determine automatically that an input value is in fact JSON data, in which case the input is treated as if it were followed by an explicit FORMAT JSON declaration. This is the case, for instance, if the value expression is itself an invocation of a SQL/JSON generation function or function json_query .
If you do not specify FORMAT JSON for a given input value, and if Oracle cannot determine that the value is JSON data, then it is assumed to be ordinary (non-JSON) SQL data. In that case it is serialized as follows (any other SQL value raises an error):
An instance of a user-defined SQL object type is rendered as a JSON object whose field names are taken from the object attribute names and whose field values are taken from the object attribute values (to which JSON generation is applied recursively).
An instance of a SQL collection type is rendered as a JSON array whose element values are taken from the collection element values (to which JSON generation is applied recursively).
A VARCHAR2 , CLOB , or NVARCHAR value is wrapped in double quotation marks ( " ), and characters are escaped when necessary to conform to the JSON standard.
A numeric value ( NUMBER , BINARY_DOUBLE , or BINARY_FLOAT ) is rendered as a JSON number. (It is not quoted.)
The numeric values of positive and negative infinity, and values that are the undefined result of a numeric operation ("not a number" or NaN ), cannot be expressed as JSON numbers. They are instead rendered as JSON strings: "Inf" , "-Inf" , and "Nan" , respectively.
A RAW or BLOB value is rendered as a hexadecimal JSON string (with double quotation marks, " ).
A time-related value ( DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , TIMESTAMP WITH LOCAL TIME ZONE , INTERVAL YEAR TO MONTH , or INTERVAL DAY TO SECOND ) is rendered in a supported ISO 8601 format, and the result is enclosed in double quotation marks ( " ).
A BOOLEAN PL/SQL value is rendered as JSON true or false . (It is not quoted.)
A NULL value is rendered as JSON null , regardless of the NULL data type. (It is not quoted.)
For data types CLOB and BLOB , an empty instance is distinguished from NULL and is rendered as an empty JSON string ( "" ). But for data types VARCHAR2 , NVARCHAR2 , and RAW , Oracle SQL treats an empty (zero-length) value as NULL , so do not expect such a value to be rendered as a JSON string.
The format of an input argument can affect the format of the data that is returned by the function. In particular, if an input is declared or automatically determined to be of format JSON then it is treated as JSON data when computing the return value. Example 23-1 illustrates this — it explicitly uses FORMAT JSON to interpret the SQL string "true" as the JSON Boolean value true .
Example 23-1 Declaring an Input Value To Be JSON
This example specifies FORMAT JSON for SQL string values 'true' and 'false' , in order that the JSON Boolean values true and false are used. Without specifying FORMAT JSON , the values of field hasCommission would be the JSON string values "true" and "false" , not the JSON Boolean values true and false .
Complex JSON Objects
Each function call can itself be an expression, so they can easily be combined to create complex JSON objects.
If we run this through a JSON Formatter, we can see the structure better.
RETURNING Clause
The SQL/JSON generation functions can optionally include a RETURNING clause to specify how the value is returned. All are capable of returning a VARCHAR2 value of varying size specified using either BYTE or CHAR . The documentation states the default return type is VARCHAR2(4000) . The JSON_OBJECTAGG and JSON_ARRAYAGG functions can optionally return their output in CLOB format.
In addition, Oracle 18c added support for CLOB and BLOB types for all SQL/JSON generation functions.
Using Numerics as Keys
The SQL/JSON functions don't accept numerics as keys.
If you need to force their use, simply use the TO_CHAR function to convert them to strings.
SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg are presented.
Topics:
You can use SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
SQL/JSON function json_object constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier . Each value of a pair can be any SQL expression . The name and value are separated by keyword VALUE .
SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions. Each argument can be any SQL expression. Array element order is the same as the argument order.
SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.
23.1 Overview of JSON Generation
An overview is presented of JSON data generation: best practices, the SQL/JSON generation functions, a simple JSON constructor syntax, handling of input SQL values, and resulting generated data.
The best way to generate JSON data from non-JSON database data is to use SQL. The standard SQL/JSON functions, json_object , json_array , json_objectagg , and json_arrayagg are designed specifically for this. If the generated data is of JSON type then a handy alternative is to use the JSON data type constructor function, JSON .
Both make it easy to construct JSON data directly from a SQL query. They allow non-JSON data to be represented as JSON objects and JSON arrays. You can generate complex, hierarchical JSON documents by nesting calls to the generation functions or constructor JSON . Nested subqueries can generate JSON collections that represent one-to-many relationships. Foot 1
The Best Way to Construct JSON Data from Non-JSON Data
Alternatives to using the SQL/JSON generation functions are generally error prone or inefficient.
Using string concatenation to generate JSON documents is error prone. In particular, there are a number of complex rules that must be respected concerning when and how to escape special characters, such as double quotation marks ( " ). It is easy to overlook or misunderstand these rules, which can result in generating incorrect JSON data.
Reading non-JSON result sets from the database and using client-side application code to generate JSON data is typically quite inefficient, particularly due to network overhead. When representing one-to-many relationships as JSON data, multiple SELECT operations are often required, to collect all of the non-JSON data needed. If the documents to be generated represent multiple levels of one-to-many relationships then this technique can be quite costly.
The SQL/JSON generation functions and constructor JSON do not suffer from such problems; they are designed for the job of constructing JSON data from non-JSON database data.
They always construct well-formed JSON documents.
By using SQL subqueries with the functions, you can generate an entire set of JSON documents using a single SQL statement, which allows the generation operation to be optimized.
Because only the generated documents are returned to a client, network overhead is minimized: there is at most one round trip per document generated.
The SQL/JSON Generation Functions
Functions json_object and json_array construct a JSON object or array, respectively. In the simplest case, json_object takes SQL name–value pairs as arguments, and json_array takes SQL values as arguments.
Functions json_objectagg , and json_arrayagg are aggregate SQL functions. They transform information that is contained in the rows of a grouped SQL query into JSON objects and arrays, respectively. Evaluation of the arguments determines the number of object members and array elements, respectively; that is, the size of the result reflects the current queried data.
For json_objectagg and json_arrayagg , the order of object members and array elements, respectively, is unspecified. For json_arrayagg , you can use an ORDER BY clause within the json_arrayagg invocation to control the array element order.
Result Returned by SQL/JSON Generation Functions
By default, the generated JSON data is returned from a generation function as a SQL VARCHAR2(4000) value. You can use the optional RETURNING clause to specify a different VARCHAR2 size or to specify a JSON , CLOB or BLOB return value instead. When BLOB is the return type, the character set is AL32UTF8.
Unless the return type is JSON , the JSON values produced from the input SQL values are serialized to textual JSON. This serialization has the same effect as Oracle SQL function json_serialize .
Handling of Input Values For SQL/JSON Generation Functions
The SQL/JSON generation functions take SQL values as input and, from them, produce JSON values inside the JSON object or array that is returned. How the input values produce the JSON values used in the output depends on their SQL data type.
Optional Behavior For SQL/JSON Generation Functions
You can optionally specify a SQL NULL -handling clause, a RETURNING clause, and keywords STRICT and WITH UNIQUE KEYS .
NULL -handling clause — Determines how a SQL NULL value resulting from input evaluation is handled.
NULL ON NULL — An input SQL NULL value is converted to JSON null for output. This is the default behavior for json_object and json_objectagg .
ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_array and json_arrayagg .
RETURNING clause — The SQL data type used for the function return value. The return type can be any of the SQL types that support JSON data: JSON , VARCHAR2 , CLOB , or BLOB . The default return type (no RETURNING clause) is VARCHAR2(4000) .
STRICT keyword — If present, the returned JSON data is checked to be sure it is well-formed. If STRICT is present and the returned data is not well-formed then an error is raised.
In general, you need not specify STRICT when generating data of JSON data type, and doing so can introduce a small performance penalty.
When an input and the returned data are both of JSON type, if you do not specify STRICT then that input is used as is in the returned data; it is not checked for strict well-formedness.
You might want to use STRICT when returning JSON type data if (1) the input data is also of JSON type and (2) you suspect that it is not completely strict. That could be the case, for example, if a client application created the input data and it did not ensure that each JSON string is represented by a valid UTF-8 sequence of bytes.
WITH UNIQUE KEYS keywords (available only for json_object and json_objectagg ) — If present, the returned JSON object is checked to be sure there are no duplicate field names. If there are duplicates, an error is raised.
If absent (or if WITHOUT UNIQUE KEYS is present) then no check for unique fields is performed. In that case:
If the return data type is JSON then only one field of a set of duplicates is used, and which is used is undefined.
If the return data type is not JSON then all fields are used, including any duplicates.
JSON Data Type Constructor
Constructor JSON can be used with a special syntax as an alternative to using json_object and json_array when generating data of data type JSON . (You can use constructor JSON and JSON type only if database initialization parameter compatible is at least 20. Otherwise an error is raised.)
The only difference in behavior is that the return data type when you use the constructor is always JSON (there is no RETURNING clause for the constructor).
When employed as an alternative syntax for json_object or json_array , you follow constructor JSON directly with braces ( <> ) and brackets ( [] ), respectively, for object and array generation, instead of the usual parentheses ( () ).
JSON < … >has the same effect as JSON( json_object( … ) ) , which has the same effect as json_object( … RETURNING JSON) .
JSON [ … ] has the same effect as JSON( json_array( … ) ) , which has the same effect as json_array( … RETURNING JSON) .
All of the behavior and syntax possibilities that json_object and json_array offer when they are used with RETURNING JSON are also available when you use constructor JSON with the special syntax. See, for example, Example 23-2, Example 23-3, Example 23-4, Example 23-5, and Example 23-6.
JSON and JSON […] provide alternative syntax only for json_object and json_array , not for the aggregate generation functions, json_objectagg and json_arrayagg . But you can of course use constructor JSON (without the special syntax) on the result of an explicit call to json_objectagg or json_arrayagg . For example, these two queries are equivalent:
Related Topics
Oracle Database SQL Language Reference for information about SQL/JSON function json_array
Oracle Database SQL Language Reference for information about SQL/JSON function json_arrayagg
Oracle Database SQL Language Reference for information about SQL/JSON function json_object
Oracle Database SQL Language Reference for information about SQL/JSON function json_objectagg
JSON_ARRAYAGG
The JSON_ARRAYAGG aggregate function, similar to the LISTAGG function, aggregates an expression from each row into a single JSON array.
JSON_OBJECT
The JSON_OBJECT function converts a comma-separated list of key-value pairs into object members within a JSON object.
19.1 Overview of SQL/JSON Generation Functions
You can use SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg to construct JSON data from non-JSON data in the database. The JSON data is returned as a SQL value.
These generation functions make it easy to construct JSON data directly from a SQL query. They allow non-JSON data to be represented as JSON objects and JSON arrays. You can generate complex, hierarchical JSON documents by nesting calls to these functions. Nested subqueries can generate JSON collections that represent one-to-many relationships. Foot 1
The Best Way to Construct JSON Data from Non-JSON Data
Alternatives to using the SQL/JSON generation functions are generally error prone or inefficient.
Using string concatenation to generate JSON documents is error prone. In particular, there are a number of complex rules that must be respected concerning when and how to escape special characters, such as double quotation marks ( " ). It is easy to overlook or misunderstand these rules, which can result in generating incorrect JSON data.
Reading non-JSON result sets from the database and using client-side application code to generate JSON data is typically quite inefficient, particularly due to network overhead. When representing one-to-many relationships as JSON data, multiple SELECT operations are often required, to collect all of the non-JSON data needed. If the documents to be generated represent multiple levels of one-to-many relationships then this technique can be quite costly.
The SQL/JSON generation functions do not suffer from such problems; they are designed for the job of constructing JSON data from non-JSON database data.
They always construct well-formed JSON documents.
By using SQL subqueries with these functions, you can generate an entire set of JSON documents using a single SQL statement, which allows the generation operation to be optimized.
Because only the generated documents are returned to a client, network overhead is minimized: there is at most one round trip per document generated.
The SQL/JSON Generation Functions
Functions json_object and json_array construct a JSON object or array, respectively, given as arguments SQL name–value pairs and values, respectively. The number of arguments corresponds to the number of object members and array elements, respectively (except when an argument expression evaluates to SQL NULL and the ABSENT ON NULL clause applies).
Each name must have the syntax of a SQL identifier. Each value can be any SQL value, including a value computed using a scalar SQL (sub)query that returns at most one item (a single row with a single column — an error is raised if such a query argument returns more than one row.)
Functions json_objectagg , and json_arrayagg are aggregate SQL functions. They transform information that is contained in the rows of a grouped SQL query into JSON objects and arrays, respectively. Evaluation of the arguments determines the number of object members and array elements, respectively; that is, the size of the result reflects the current queried data.
For json_objectagg , the order of object members is unspecified. For json_arrayagg , the order of array elements reflects the query result order. You can use SQL ORDER BY in the query to control the array element order.
Formats of Input Values for JSON_OBJECT and JSON_ARRAY
For function json_array you can use any SQL value of the supported data types as arguments. Similarly for the value arguments of name–value pairs that you pass to function json_object . In some cases you know or expect that such a value is in fact JSON data (represented as a SQL string or number). You can add keywords FORMAT JSON after any input value expression to declare this expectation for the value that results from that expression.
If Oracle can determine that the value is in fact JSON data then it is treated as if it were followed by an explicit FORMAT JSON declaration. This is the case, for instance, if the value expression is an invocation of a SQL/JSON generation function.
A VARCHAR2 or CLOB value is wrapped in double quotation marks ( " ).
A numeric value is converted to a JSON number. (It is not quoted.)
A DATE or TIMESTAMP value is converted to ISO 8601 format, and the result is enclosed in double quotation marks ( " ).
A BOOLEAN PL/SQL value is converted to JSON true or false . (It is not quoted.)
A NULL value is converted to JSON null , regardless of the NULL data type.
Because Oracle SQL treats an empty string as NULL there is no way to construct an empty JSON string ( "" ).
The format of an input argument can affect the format of the data that is returned by the function. In particular, if an input is determined to be of format JSON then it is treated as JSON data when computing the return value. Example 19-1 illustrates this — it explicitly uses FORMAT JSON to interpret the SQL string "true" as JSON Boolean value true .
Optional Behavior For SQL/JSON Generation Functions
You can optionally specify a SQL NULL -handling clause, a RETURNING clause, and keyword STRICT .
NULL -handling clause — Determines how a SQL NULL value resulting from input evaluation is handled.
NULL ON NULL — An input SQL NULL value is converted to JSON null for output. This is the default behavior for json_array and json_arrayagg .
ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_object and json_objectagg .
RETURNING clause — The SQL data type used for the function return value. The default is VARCHAR2(4000) .
STRICT keyword — If present, the returned JSON data is checked, to be sure it is well-formed. If STRICT is present and the returned data is not well-formed then an error is raised.
Result Returned by SQL/JSON Generation Functions
The generated JSON data is returned from the function as a SQL VARCHAR2 value, whose size can be controlled by the optional RETURNING clause. For the aggregate SQL functions ( json_objectagg and json_arrayagg ), you can also specify CLOB as the SQL data type in the RETURNING clause.
JSON values within the returned data are derived from SQL values in the input as follows:
A SQL number is converted to a JSON number.
A non- NULL and non-number SQL value is converted to a JSON string.
A SQL NULL value is handled by the optional NULL -handling clause.
Oracle Database SQL Language Reference for information about SQL/JSON function json_array
Oracle Database SQL Language Reference for information about SQL/JSON function json_arrayagg
Oracle Database SQL Language Reference for information about SQL/JSON function json_object
Oracle Database SQL Language Reference for information about SQL/JSON function json_objectagg
Example 19-1 Declaring an Input Value To Be JSON
This example specifies FORMAT JSON for SQL string values 'true' and 'false' , in order that the JSON Boolean values true and false are used.
19.2 JSON_OBJECT SQL/JSON Function
SQL/JSON function json_object constructs JSON objects from name–value pairs. Each pair is provided as an explicit argument. Each name of a pair must evaluate to a SQL identifier . Each value of a pair can be any SQL expression . The name and value are separated by keyword VALUE .
The evaluated arguments you provide to json_object are explicit object field names and field values. The resulting object has an member for each pair of name–value arguments you provide (except when an value expression evaluates to SQL NULL and the ABSENT ON NULL clause applies).
Example 19-2 Using JSON_OBJECT to Construct JSON Objects
This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR ) whose salary is less than 15000. The object includes, as the value of its field contactInfo , an object with fields mail and phone .
Because the return value of json_object is JSON data, FORMAT JSON is deduced for the input format of field contactInfo — the explicit FORMAT JSON here is not needed.
Example 19-3 Using JSON_OBJECT With ABSENT ON NULL
This example queries table hr.locations from standard database schema HR to create JSON objects with fields city and province .
The default NULL -handling behavior for json_object is NULL ON NULL .
In order to prevent the creation of a field with a null JSON value, the example uses ABSENT ON NULL . The NULL SQL value for column state_province when column city has value 'Singapore' means that no province field is created for that location.
You can use SQL to generate JSON objects and arrays from non-JSON data in the database. For that, use either constructor JSON or SQL/JSON functions json_object , json_array , json_objectagg , and json_arrayagg .
An overview is presented of JSON data generation: best practices, the SQL/JSON generation functions, a simple JSON constructor syntax, handling of input SQL values, and resulting generated data.
The SQL/JSON generation functions take SQL values as input and return a JSON object or array. The input values are used to produce JSON object field–value pairs or JSON array elements. How the input values are used depends on their SQL data type.
SQL/JSON function json_object constructs JSON objects from the results of evaluating its argument SQL expressions.
SQL/JSON function json_array constructs a JSON array from the results of evaluating its argument SQL expressions.
SQL/JSON function json_objectagg constructs a JSON object by aggregating information from multiple rows of a grouped SQL query as the object members.
SQL/JSON function json_arrayagg constructs a JSON array by aggregating information from multiple rows of a grouped SQL query as the array elements. The order of array elements reflects the query result order, by default, but you can use the ORDER BY clause to impose array element order.
Читайте также: