HOW DO I Use SQL to Extract JSON Data

 Introduction

Working with JSON data in Oracle APEX has become increasingly common, especially when integrating with RESTful Web Services, external APIs, or complex application logic. Oracle SQL provides powerful tools to parse and extract data directly from JSON documents stored in CLOB, VARCHAR2, or native JSON columns. This blog post explains how to use SQL to extract JSON data efficiently within APEX, from simple key-value pairs to nested arrays.

How to Extract JSON Data Using SQL in Oracle APEX

To extract data from a JSON structure in SQL, you can use built-in JSON functions and SQL/JSON query operators like JSON_VALUE, JSON_QUERY, and JSON_TABLE.

1. JSON_VALUE for Scalar Extraction
Use JSON_VALUE when you want to extract a single scalar value (e.g., string, number).

Example:

SELECT JSON_VALUE(json_column, '$.employee.name') AS employee_name
FROM my_json_table;

This retrieves the name value inside the employee object.

2. JSON_QUERY for JSON Fragments
Use JSON_QUERY when you need to retrieve a JSON object or array as a JSON string.

Example:

SELECT JSON_QUERY(json_column, '$.employee.projects') AS project_list
FROM my_json_table;

This extracts the projects array from the JSON data.

3. JSON_TABLE for Relational Mapping
JSON_TABLE is ideal for turning nested JSON into a relational result set.

Example:

SELECT *
FROM JSON_TABLE (
  (SELECT json_column FROM my_json_table),
  '$.employee.projects[*]'
  COLUMNS (
    project_name VARCHAR2(100) PATH '$.name',
    start_date   DATE          PATH '$.start'
  )
) jt;

This maps an array of projects into individual rows, with project_name and start_date columns.

4. Querying JSON Stored in APEX Collections or CLOBs
If you're storing JSON in an APEX collection or CLOB column, you can still use these JSON functions, provided the content is valid JSON.

Example:

SELECT JSON_VALUE(c001, '$.customer.email') AS email
FROM apex_collections
WHERE collection_name = 'MY_JSON_DATA';

Best Practices

• Always validate JSON syntax before querying, especially when using user-supplied data.

• Use indexes on JSON columns to improve performance (available via Oracle’s JSON search indexes).

• Prefer JSON_TABLE when you need structured or tabular outputs.

• Avoid using LIKE '%...%' on JSON; use JSON functions instead.

• Use Oracle’s IS JSON condition to ensure content validity.

Oracle APEX Documentation

• Working with JSON Data in Oracle Database

• APEX_JSON PL/SQL API Reference 

 

You can write SQL queries in APEX to extract and display the JSON data from the CLOB column ("GEOMETRY") using Oracle’s JSON functions.

Here’s an example of how to query the JSON data stored in the "GEOMETRY" column to display specific values.

Query to extract the "type" and "coordinates" from the "GEOMETRY" JSON column:

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.type') AS geometry_type,

    JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS longitude,

    JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS latitude

FROM USCG_DATA;

In this example:

• JSON_VALUE(GEOMETRY, '$.type'): Extracts the "type" field from the JSON.

• JSON_VALUE(GEOMETRY, '$.coordinates[0]'): Extracts the longitude from the "coordinates" array (first element).

• JSON_VALUE(GEOMETRY, '$.coordinates[1]'): Extracts the latitude from the "coordinates" array (second element).

You can use the above SQL query to display the extracted data in an APEX report, Interactive Grid, or other components.

Example: Displaying the Data in an Interactive Report (IR)

1. Create an Interactive Report (IR): 

• Go to App Builder in your Oracle APEX application.

• Create a new Interactive Report or Interactive Grid.

• In the SQL Query section of the report, use the query mentioned above:

SELECT

    ID,

    JSON_VALUE(GEOMETRY, '$.type') AS geometry_type,

    JSON_VALUE(GEOMETRY, '$.coordinates[0]') AS longitude,

    JSON_VALUE(GEOMETRY, '$.coordinates[1]') AS latitude

FROM USCG_DATA;

• Run the report, and it will display the "ID", "geometry_type", "longitude", and "latitude" in the report.

Conclusion
Using SQL to extract JSON data in Oracle APEX provides a seamless way to interact with complex data structures and REST service responses. By mastering functions like JSON_VALUE, JSON_QUERY, and JSON_TABLE, developers can efficiently transform, analyze, and present JSON data in reports, charts, and forms. Leveraging these tools within APEX applications allows for powerful and dynamic user experiences built on modern data exchange formats.