JSON_SERIALIZE

Syntax

JSON_SERIALIZE(json_expr [JSON_SERIALIZE_return_clause] [PRETTY] [ASCII] [TRUNCATE][JSON_SERIALIZE_on_error])

JSON_SERIALIZE_return_clause::= RETURNING {BLOB|CLOB|NCLOB|NVARCHAR2 [(size)]|[VARCHAR2 [(size [BYTE|CHAR])]}
JSON_SERIALIZE_on_error::= {NULL|ERROR|EMPTY [empty_clause]} ON ERROR
empty_clause::= ARRAY|OBJECT

Parameters

JSON_SERIALIZE has the parameters:

Parameter	Description
`json_expr`	Input to the `JSON_SERIALIZE` function. The `json_expr` parameter can be of SQL data type `VARCHAR2`, `NVARCHAR2`, `CLOB`, `NCLOB`, `JSON`, or `BLOB` and must be valid JSON.
`JSON_SERIALIZE_return_clause`	Optional clause that allows you to specify the return type of the output. The default is `VARCHAR2(4000)`. The supported return types are: `BLOB` `CLOB` `NCLOB` `NVARCHAR2`: For `NVARCHAR2`, you can optionally specify the size. If you do not specify a size, the default is `4000`. `VARCHAR2`: For `VARCHAR2`, you can optionally specify the size and the semantics (`BYTE` or `CHAR`). If you do not specify a size, the default is `4000`. The default semantics is `BYTE`.
`PRETTY`	Optional clause that enables human readability formatting by including line breaks and indentation.
`ASCII`	Optional clause that enables non-ASCII characters to be output using JSON escape sequences.
`TRUNCATE`	Optional clause that handles cases where the output is too large to fit into the buffer of the specified (or default) return type. If the output is too large to fit into the return type, the value is truncated to fit into the buffer of the return type.
`JSON_SERIALIZE_on_error`	Optional clause that enables error handling control. If not specified, the default is `ERROR` `ON` `ERROR`. Valid clauses are `NULL` `ON` `ERROR`, `ERROR` `ON` `ERROR`, and `EMPTY` `ON` `ERROR` (described in the rows that follow).
`NULL` `ON` `ERROR`	If there is an error, and `NULL` `ON` `ERROR` is specified, `JSON_SERIALIZE` returns `NULL` and does not return the error.
`ERROR` `ON` `ERROR`	If there is an error, and `ERROR` `ON` `ERROR` is specified, `JSON_SERIALIZE` returns the error. This is the default.
`EMPTY [empty_clause] ON ERROR`	If there is an error, and the `EMPTY` `ON` `ERROR` clause is specified, `JSON_SERIALIZE` returns either an empty array or an empty object and does not return the error. Valid options for the `empty_clause` are: `ARRAY`: Returns an empty array. This is the default. `OBJECT`: Returns an empty object.

Description

The JSON_SERIALIZE function takes JSON data of various SQL types as input and returns a textual representation of it.
You can use the JSON_SERIALIZE function to convert binary JSON data to textual form or to transform textual JSON data by pretty-printing it or escaping non-ASCII unicode characters in it.
You can specify the JSON_SERIALIZE_return_clause, JSON_SERIALIZE_on_error, PRETTY, ASCII, and TRUNCATE optional clauses in any order.
If you specify the TRUNCATE clause and the JSON_SERIALIZE_on_error clause, and if the value is too large to fit into the buffer of the return type, JSON_SERIALIZE truncates the value to fit into the buffer and does not raise an error. For example, if you specify TRUNCATE and ERROR ON ERROR, JSON_SERIALIZE truncates the value and does not return an error.

Examples

The following examples illustrate various uses of the JSON_SERIALIZE function.

The examples use ttIsql and are run from the ttIsql Command> prompt.

Let's create a table and store data in a column of type JSON.

CREATE TABLE json_doc (id TT_INTEGER, data JSON);
INSERT INTO json_doc values (1, '{"id":1,"first_name":"John","last_name":"Smith"}');

The output shows one row is inserted into the table:

1 row inserted.

Let's use the JSON_SERIALIZE function to convert the data of type JSON to textual form. Since there is no RETURNING clause, the default return type is VARCHAR2 (4000).

SELECT JSON_SERIALIZE (data) FROM json_doc;

The output is the following:

< {"id":1,"first_name":"John","last_name":"Smith"} >
1 row found.

Examples Using the Optional Clauses

Let's experiment with the PRETTY clause.

Let's first omit it.

SELECT JSON_SERIALIZE ('{"id":1,"first_name":"John","last_name":"Smith"}');

The output is the following:

< {"id":1,"first_name":"John","last_name":"Smith"} >
1 row found.

Next, let's specify the PRETTY keyword, which pretty-prints the output in human readable form with line breaks and indentation.

SELECT JSON_SERIALIZE ('{"id":1,"first_name":"John","last_name":"Smith"}' PRETTY);

The output is as follows:

< {
  "id" : 1,
  "first_name" : "John",
  "last_name" : "Smith"
} >
1 row found.

Let's use the RETURNING clause to specify the return type of the output. This example specifies VARCHAR2 (100).

SELECT JSON_SERIALIZE ('{"id":1,"first_name":"John","last_name":"Smith"}' RETURNING VARCHAR2 (100));

The output is as follows:

< {"id":1,"first_name":"John","last_name":"Smith"} >
1 row found.

Let's verify the return type is VARCHAR2 (100).

describe SELECT JSON_SERIALIZE ('{"id":1,"first_name":"John","last_name":"Smith"}' RETURNING VARCHAR2 (100));

The output is as follows:

Prepared Statement:
  Columns:
    EXP                             VARCHAR2 (100) NOT NULL

The TRUNCATE keyword truncates the output to fit into the buffer of the return type. In the following example, since the return type is VARCHAR2(10), the output is truncated to fit into the buffer of a VARCHAR2 (10) return type.

SELECT JSON_SERIALIZE ('{"id":1,"first_name":"John","last_name":"Smith"}' TRUNCATE RETURNING VARCHAR2 (10));

The output is the following:

< {"id":1,"f >
1 row found.

Let's create a table and store JSON data in a column of type BLOB.

CREATE TABLE json_doc2 (id TT_INTEGER, data BLOB);
INSERT INTO json_doc2 values (1, '{"id":1,"first_name":"John","last_name":"Smith"}');

The output shows one row is inserted into the table:

1 row inserted.

Let's SELECT the data to illustrate that the result is of type BLOB.

SELECT data FROM json_doc2;

The output is the following:

< 7B226964223A312C2266697273745F6E616D65223A224A6F686E222C226C6173745F6E616D65223A22536D697468227D >
1 row found.

Let's use the JSON_SERIALIZE function to convert the JSON data of type BLOB to textual form. Let's specify a VARCHAR2 (200) return type.

SELECT JSON_SERIALIZE (data RETURNING VARCHAR2 (200)) FROM json_doc2;

The output is the following:

< {"id":1,"first_name":"John","last_name":"Smith"} >
1 row found.

Error Handling Examples

The following examples illustrate various uses of the ON ERROR clause.

Here is an example that has a syntax error. Since the default is ERROR ON ERROR, JSON_SERIALIZE returns an error.

SELECT JSON_SERIALIZE ('This is not JSON!');

The output is as follows:

 2379: JSON syntax error : JZN-00078: Invalid JSON keyword 'This'
The command failed.

Let's use the same JSON syntax, but change the ON ERROR clause to NULL ON ERROR. Even though there is a syntax error, JSON_SERIALIZE returns NULL due to the NULL ON ERROR clause.

SELECT JSON_SERIALIZE ('This is not JSON!' NULL ON ERROR);

The output is as follows:

< <NULL> >
1 row found.

Let's use the same JSON syntax, but change the ON ERROR clause to EMPTY ON ERROR. Even though there is a syntax error, JSON_SERIALIZE returns an empty array due to the EMPTY ON ERROR clause. When using the EMPTY ON ERROR clause, the default is EMPTY ARRAY ON ERROR.

SELECT JSON_SERIALIZE ('This is not JSON!' EMPTY ON ERROR);

The output is as follows:

< [] >
1 row found.

Let's specify the TRUNCATE keyword with the ERROR ON ERROR clause. If the output is too large to fit into the buffer of the return type, JSON_SERIALIZE truncates the output to fit into the buffer instead of raising an error.

SELECT JSON_SERIALIZE ('{"Name" : [1,2,3,4]}' RETURNING VARCHAR2 (4) TRUNCATE ERROR ON ERROR);

The output is as follows:

< {"Na >
1 row found.