Oracle JSON_QUERY Function

When working with JSON data in Oracle, you have two main functions for extracting data: JSON_VALUE and JSON_QUERY.

• JSON_VALUE: Gets a single scalar value (like a string or a number).
• JSON_QUERY: Gets a JSON fragment (an object or an array).

This tutorial focuses on JSON_QUERY, which you use when you want to extract a piece of the JSON itself, not just a simple value.

What is the JSON_QUERY Function in Oracle?

The JSON_QUERY function searches JSON data for a specific path and returns the JSON object or array found at that path.

This is the function you need when you want to:

• Extract a nested object (e.g., a billing_address object from a customer).
• Extract an array of items (e.g., an items array from an order).
• Retrieve a list of values that match a path.

One of the most important clauses for this function is the WRAPPER clause, which is often required.

JSON_QUERY Function Syntax

The basic syntax for JSON_QUERY is:

JSON_QUERY(
    json_expr, 
    path_expression 
    [ RETURNING data_type ]
    [ wrapper_clause ] 
    [ on_error_clause ] 
    [ on_empty_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 data you want (e.g., $.billing_address).
• [wrapper_clause]: This is critical.
  • WITHOUT WRAPPER (Default): Use this when your path is guaranteed to return a single object or array (e.g., $.billing_address).
  • WITH WRAPPER: You must use this if your path could return multiple values (e.g., $.items[*].name) or a single scalar value (e.g., $.items[0].name). It wraps the result in square brackets [].

Oracle JSON_QUERY 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 JSON Object using JSON_QUERY

This is the most common use case. We want to extract a nested object (the billing_address) from a JSON string. Since the path $.billing_address returns a single object, we can use the default WITHOUT WRAPPER clause.

Query:

SELECT
  JSON_QUERY(
    '{"user": "JohnDoe", "billing_address": {"street": "123 Main St", "city": "Anytown"}}',
    '$.billing_address'
  ) AS "Billing_Address_Object"
FROM DUAL;

Result: (Notice the result is a JSON string, not a simple value)

Billing_Address_Object
--------------------------------
{"street":"123 Main St","city":"Anytown"}

Example 2: Extracting Multiple Values with WITH WRAPPER using JSON_QUERY

Now, let's extract all the product names from an items array. The path $.items[*].product will find two scalar values: "Widget" and "Gadget".

Because the path returns multiple values, you must use the WITH WRAPPER clause to wrap the results in a JSON array.

Query:

SELECT
  JSON_QUERY(
    '{"items": [{"id": 1, "product": "Widget"}, {"id": 2, "product": "Gadget"}]}',
    '$.items[*].product' WITH WRAPPER
  ) AS "Product_List"
FROM DUAL;

Result: (The two values are returned, wrapped in a [] array)

Product_List
--------------------
["Widget","Gadget"]

Example 3: Extracting a Single Scalar Value using JSON_QUERY

What if your path points to a single string or number (a "scalar" value)? By default, JSON_QUERY will fail or return NULL, as it's designed to return objects or arrays.

To fix this, you must use the WITH WRAPPER clause. This tells Oracle to wrap the single scalar value in an array.

Query:

SELECT
  JSON_QUERY(
    '{"user": "JohnDoe", "status": "Active"}',
    '$.status' WITH WRAPPER
  ) AS "Status_Value"
FROM DUAL;

Result: (The single value "Active" is returned, wrapped in an array.)

Status_Value
------------
["Active"]