Oracle JSON_VALUE Function

When working with JSON data in Oracle, you have two main functions for extracting data: JSON_VALUE and JSON_QUERY. The difference is simple but critical:

• JSON_QUERY: Gets a JSON fragment (an object or an array, like {"a":1} or [1,2,3]).
• JSON_VALUE: Gets a single scalar value (a simple string or number, like 'John' or 30).

This tutorial focuses on JSON_VALUE, which is the function you use when you want to pull a single value out of a JSON document and use it as a standard SQL data type.

What is the JSON_VALUE Function in Oracle?

The JSON_VALUE function searches JSON data for a specific path, finds the scalar value at that path, and returns it as a standard SQL data type like VARCHAR2 or NUMBER.

This is the function you need when you want to:

• Extract a "name" as a VARCHAR2.
• Extract an "age" or "price" as a NUMBER.
• Extract a "hire_date" as a DATE.

It's most commonly used in a SELECT list or a WHERE clause to treat a value from a JSON document just like a regular column.

JSON_VALUE Function Syntax

The syntax for JSON_VALUE can be simple or advanced, but the most common parts are:

JSON_VALUE(
    json_expr, 
    path_expression 
    [ RETURNING data_type ] 
    [ default_on_error_clause ] 
)

Let's break down the key parts:

• json_expr: The column or string containing your JSON data.
• path_expression: The SQL/JSON path to the single value you want (e.g., $.name).
• [RETURNING data_type] (Optional): This is highly recommended. It specifies the SQL data type you want the value returned as (e.g., RETURNING NUMBER).
  • If you omit this, the function returns a VARCHAR2(4000), which is often not what you want for numbers or dates.
• [default_on_error_clause] (Optional): This is a powerful safety feature.
  • DEFAULT 0 ON ERROR: If the path is not found or the value can't be converted, it returns 0 instead of an error.
  • DEFAULT NULL ON ERROR: (This is the default) Returns NULL if an error occurs.

Oracle JSON_VALUE Function Examples

All examples below use the DUAL table, which is a simple one-row table in Oracle for testing functions.

Example 1: Extracting a Text Value using JSON_VALUE

This is the most basic use case. We want to extract the name from a JSON string. Since the default return type is VARCHAR2, we don't need a RETURNING clause.

Query:

SELECT
  JSON_VALUE(
    '{"name": "John Doe", "age": 30, "active": true}',
    '$.name'
  ) AS "Name"
FROM DUAL;

Result: (The query returns the simple text value.)

Name
----------
John Doe

Example 2: Using RETURNING NUMBER and Handling Errors with JSON_VALUE

This example shows two key features:

1. We use RETURNING NUMBER to convert the age value to a NUMBER data type, so we could perform math on it.
2. We try to find a salary path, which doesn't exist. By using DEFAULT 0 ON ERROR, we prevent the query from failing and get a 0 instead.

Query:

SELECT
  JSON_VALUE(
    '{"name": "John Doe", "age": 30, "active": true}',
    '$.age' RETURNING NUMBER
  ) AS age,
  JSON_VALUE(
    '{"name": "John Doe", "age": 30, "active": true}',
    '$.salary' RETURNING NUMBER DEFAULT 0 ON ERROR
  ) AS salary
FROM dual;

Result: (The age is returned as a NUMBER, and the missing salary is returned as 0.)

       Age     Salary
---------- ----------
        30          0