Oracle JSON() Function

Oracle JSON() is a built-in function that parses textual JSON input (a scalar, object, or array), and returns it as an instance of type JSON.

Oracle `JSON()` Syntax

Here is the syntax for the Oracle JSON() function:

JSON(expr)

Parameters

expr: Required. The input in expr must be a syntactically valid textual representation of type VARCHAR2, CLOB, and BLOB. It can also be a literal SQL string. A SQL NULL input value results in a JSON type instance of SQL NULL.

Return Value

The Oracle JSON() function returns an instance of the JSON type based on the input parameter.

Input values must pass the IS JSON test. Input values that fail the IS JSON test are rejected with a syntax error.

To filter out duplicate input values, you must run the IS JSON (WITH UNIQUE KEYS) check on the textual JSON input before using the JSON constructor.

You can use the constructor JSON only if database initialization parameter compatible is at least 20.

If any of the parameters are NULL, JSON() returns a JSON value of null.

Oracle `JSON()` Examples

Here are a few examples that demonstrate how to use the Oracle JSON() function.

Example 1

SELECT
    JSON('123'),
    JSON('"a"'),
    JSON('true'),
    JSON('false')
FROM dual;

输出：

JSON('123')    JSON('"A"')    JSON('TRUE')    JSON('FALSE')
______________ ______________ _______________ ________________
123            "a"            true            false

Example 2

SELECT
    JSON('[1,2,3]'),
    JSON('{"x":1, "y":2}')
FROM dual;

输出：

JSON('[1,2,3]')    JSON('{"X":1,"Y":2}')
__________________ ________________________
[1,2,3]            {"x":1,"y":2}

NULL Parameter

If any of the parameters are NULL, JSON() returns a JSON value of null.

SELECT
    JSON(NULL),
    JSON('NULL')
FROM dual;

输出：

JSON(NULL)    JSON('NULL')
_____________ _______________
null          null

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Conclusion