Oracle JSON Functions Explained: JSON_QUERY, JSON_TABLE & 23c Enhancements

Introduction

JSON has become a core part of modern applications, powering APIs, microservices, and data exchange across systems. As developers increasingly work with JSON, databases must evolve to handle it efficiently.

Oracle Database has stepped up with powerful JSON functions that allow you to:

• Query JSON data
• Convert JSON into relational format
• Extract values easily
• Integrate JSON with SQL seamlessly

In this blog, we’ll explore key Oracle JSON functions like:

• JSON_QUERY
• JSON_TABLE
• JSON_VALUE

And take a look at exciting Oracle 23c JSON enhancements.

---

Why JSON Matters in Oracle

Traditionally, databases were designed for structured data. But today:

• APIs return JSON
• Applications store JSON
• Microservices rely on JSON

Oracle bridges this gap by allowing you to:

👉 Treat JSON as both document-based and relational data

---

JSON_QUERY: Extract JSON Objects

The JSON_QUERY function is used to:

👉 Retrieve JSON objects or arrays from JSON data

---

How It Works

• Accepts JSON data
• Uses a path expression
• Returns JSON as a character string

---

Example

SELECT JSON_QUERY(json_column, '$.employee')
FROM employees_json;

---

Key Features

• Returns JSON format output
• Maintains proper JSON structure
• Automatically formats keys with double quotes

---

When to Use JSON_QUERY

• Extract full JSON objects
• Work with nested JSON
• Retrieve arrays or structured data

---

JSON_VALUE: Extract Scalar Values

The JSON_VALUE function is used to:

👉 Extract a single value from JSON

---

Example

SELECT JSON_VALUE(json_column, '$.employee.name')
FROM employees_json;

---

Key Behavior

• Returns a scalar value (not JSON object)
• Default return type: VARCHAR2(4000)
• Can be customized using RETURNING clause

---

When to Use JSON_VALUE

• Extract individual fields
• Retrieve simple values like:
  • Name
  • Salary
  • ID

---

JSON_QUERY vs JSON_VALUE

Feature	JSON_QUERY	JSON_VALUE
Output Type	JSON Object/Array	Scalar Value
Use Case	Complex JSON	Single Value
Format	JSON String	Plain Value

---

JSON_TABLE: Convert JSON to Relational Data

The JSON_TABLE function is one of the most powerful features in Oracle.

👉 It transforms JSON into a relational table format

---

Why It’s Powerful

• Converts JSON arrays into rows
• Extracts fields into columns
• Allows SQL queries on JSON data

---

Example

SELECT *
FROM JSON_TABLE(
  json_column,
  '$.employees[*]'
  COLUMNS (
    name VARCHAR2(100) PATH '$.name',
    salary NUMBER PATH '$.salary'
  )
);

---

What Happens?

• Each JSON object → One row
• JSON fields → Columns

---

Key Rules

• Must be used in the FROM clause
• Acts like a virtual table
• Can be joined with other tables

---

Real-World Use Cases

1. API Data Processing

Convert API JSON responses into SQL tables.

2. Reporting

Generate reports from JSON-based systems.

3. Data Integration

Combine JSON and relational data seamlessly.

---

Oracle 23c JSON Enhancements

Oracle 23c introduces powerful upgrades for JSON handling.

---

1. JSON Schema Support

• Validate JSON structure
• Enforce data consistency
• Improve data quality

---

2. JSON-Relational Duality View

This is a game-changing feature.

👉 Allows data to be viewed as both:

• JSON document
• Relational table

---

Why It Matters

• Developers can use JSON
• DBAs can use relational models
• Best of both worlds

---

3. Sorting JSON Data

You can now:

• Sort JSON values directly
• Use ORDER BY with JSON

---

4. Comparing JSON Data

Oracle 23c enables:

• Direct comparison of JSON values
• Improved query flexibility

---

Supporting Functions You Should Know

Alongside JSON functions, Oracle provides useful utilities:

---

Data Type Conversion Functions

• TO_CHAR → Convert to string
• TO_NUMBER → Convert to number
• TO_DATE → Convert to date

---

NULL Handling Functions

• NVL → Replace NULL values
• NVL2 → Conditional NULL logic

---

Conditional Expressions

• CASE → Apply logic in SQL

---

Why These Matter

When working with JSON:

• Data types may vary
• NULL values may appear
• Conditions may be required

These functions help clean and control your data.

---

Best Practices for JSON in Oracle

✔ Use JSON_TABLE for Complex Data

Best for transforming JSON into relational format.

✔ Use JSON_VALUE for Performance

Faster when extracting single values.

✔ Validate JSON with Schema

Ensure data consistency.

✔ Combine JSON with SQL

Leverage full database power.

---

Common Mistakes to Avoid

• ❌ Using JSON_QUERY for scalar values
• ❌ Forgetting JSON_TABLE must be in FROM clause
• ❌ Ignoring data type conversions
• ❌ Not validating JSON structure

---

Conclusion

Oracle’s JSON capabilities have evolved significantly, making it easier than ever to:

• Query JSON data
• Transform it into relational format
• Integrate with existing SQL workflows

With functions like:

• JSON_QUERY
• JSON_VALUE
• JSON_TABLE

And enhancements in Oracle 23c, you can now build modern, flexible, and scalable data solutions.

---

Final Thought 🚀

JSON is no longer just for applications—it’s a first-class citizen in Oracle.

Mastering these functions will give you a powerful edge in today’s data-driven world.