Oracle JSON_SERIALIZE Function

The JSON_SERIALIZE function in Oracle SQL is a conversion function that takes JSON data (stored as a JSON type, VARCHAR2, CLOB, or BLOB) and converts it back into a textual representation.

This function is not for querying JSON (like JSON_VALUE or JSON_QUERY). Instead, it is used for formatting the output. Its main purpose is to "export" JSON data from the database in a specific, readable text format.

This is useful for:

• Converting native, binary JSON data back into a readable string.
• Pretty-printing JSON with indentation and newlines to make it human-readable.
• Sorting the keys in a JSON object alphabetically for consistent output.
• Escaping non-ASCII characters (like € or Ä) into their \uXXXX Unicode format for safe transport.

JSON_SERIALIZE Function Syntax

The syntax for JSON_SERIALIZE is:

JSON_SERIALIZE(
    json_expr
    [ RETURNING data_type ]
    [ PRETTY ]
    [ ASCII ]
    [ ORDERED ]
    [ TRUNCATE ]
)

Let's break that down:

• json_expr: The JSON data or column (of type JSON, VARCHAR2, CLOB, or BLOB) that you want to convert to text.
• RETURNING data_type (Optional): Specifies the output data type, such as VARCHAR2(100) or CLOB. The default is VARCHAR2(4000).
• PRETTY (Optional): Formats the output string with indentation and newlines.
• ASCII (Optional): Converts non-ASCII characters into their \uXXXX escape codes.
• ORDERED (Optional): Sorts the keys in all JSON objects alphabetically.
• TRUNCATE (Optional): If the output string is too long for the RETURNING data type (e.g., a 500-byte string for VARCHAR2(100)), this will cut the string short instead of raising an error.

Oracle JSON_SERIALIZE Function Examples

Here are two practical examples of how to use JSON_SERIALIZE.

Example 1: Using PRETTY, ASCII, and ORDERED with JSON_SERIALIZE

This is a very common use case. We have a simple JSON object, and we want to format it for display in a log or a file. We will make it pretty, sort the keys alphabetically (currency before price), and convert the € symbol to its ASCII-safe \u20AC code.

Query:

SELECT
  JSON_SERIALIZE(
    '{price:20, currency:"€"}' PRETTY ASCII ORDERED
  ) AS "Formatted_JSON"
FROM DUAL;

Result: (Notice the keys are now in alphabetical order, it's pretty-printed, and the € is escaped)

Formatted_JSON
--------------------
{
  "currency" : "\u20AC",
  "price" : 20
}

Example 2: Using TRUNCATE with JSON_SERIALIZE

This example shows how to prevent an error when the formatted JSON is too large for the specified return type. We will try to serialize a JSON string into a VARCHAR2(3), which is too small. The TRUNCATE clause will cut the result short instead of raising an ORA-40478 error.

Query:

SELECT
  JSON_SERIALIZE(
    '{a:[1,2,3,4]}' RETURNING VARCHAR2(3) TRUNCATE
  ) AS "Truncated_JSON"
FROM DUAL;

Result: (The full string {"a":... is cut off after 3 characters)

Trun
----
{"a