Can SQL Understand JSON?

Yuvaraj Muniyandi

· 11th September 2026 · 4 mins read

An e-commerce platform logs order events from its checkout API. Each event arrives as JSON: an order ID, customer details, a list of line items, sometimes optional fields depending on which part of checkout fired the event. The team wants to join this event data against the relational order and customer tables that already live in Exasol, to answer questions like which customers abandoned checkout after adding an item over $100.

The JSON doesn’t fit the existing schema, so a conventional approach is to ship it to a separate processing step: a script that parses the JSON, flattens it into columns, and loads the result into a new table before anyone can query it. Every time the event schema shifts, that flattening step needs updating too, and new events aren’t available in the flattened table until that job runs.

Exasol takes a different approach. JSON functions let you extract values from JSON directly in SQL and use them alongside relational data without a separate parsing job.

Following order events through SQL

Say the JSON events land in a table with an ID and a VARCHAR column holding the raw JSON string:

CREATE OR REPLACE TABLE order_events (
    id INT,
    json VARCHAR(2000000)
);

INSERT INTO order_events
VALUES (1, '{"order_id": 1001, "customer": "Smith"}');

INSERT INTO order_events
VALUES (2, '{"order_id": 1002, "customer": "Doe"}');

From there, the workflow has four steps.

Extract a value. JSON_VALUE pulls a single scalar from a JSON string at a given path.

SELECT
    id,
    JSON_VALUE(
        json,
        '$.customer' NULL ON EMPTY DEFAULT 'invalid name' ON ERROR
    ) AS customer
FROM order_events;

The ON EMPTY and ON ERROR clauses control what happens when a path doesn’t resolve or the JSON is malformed, so a missing field or a bad payload doesn’t need to fail the whole query.

Extract several values. Where JSON_VALUE returns one scalar per call, JSON_EXTRACT can return multiple fields from the same JSON string in one call, each mapped to a typed output column through an EMITS clause:

SELECT id,
       e.order_id,
       e.customer
FROM order_events,
     JSON_EXTRACT(json, '$.order_id', '$.customer')
       EMITS (order_id INT, customer VARCHAR(100)) e;

Each path lines up with the column defined in the same position in EMITS, so the order ID and customer come out of one call instead of two.

Navigate nested structure. Order events often carry a line-items array nested inside the JSON. JSON path expressions can also navigate nested objects and arrays. For example, $.items[*].sku addresses the SKU value in each item in an items array.

Use it alongside relational data. Once extracted, a JSON-derived value can be used in SQL expressions and joined against relational tables in the same query.

SELECT o.order_id, o.total, e.customer
FROM orders o
JOIN order_events e
  ON o.order_id = JSON_VALUE(e.json, '$.order_id' RETURNING INT);

This is the pattern needed to combine JSON-derived values with relationalorder data. Extracted JSON fields sit in the same query as relational columns, joined and filtered using standard SQL.

Why the extraction step matters

JSON often contains fields you eventually need to filter, compare, aggregate, or join with relational data. Extraction functions make those fields available to SQL for those operations.

An extracted order ID, customer name, SKU, or other JSON value can then be used alongside relational columns in SELECT, JOIN, and WHERE clauses. Developers can work with the JSON fields they need without first maintaining a separate flattened copy of the entire payload.

Two functions, one workflow

One field → JSON_VALUE
Several fields → JSON_EXTRACT

JSON_VALUE provides single-value extraction. It’s suited to pulling one scalar from a JSON string, with explicit handling for missing or malformed paths.

JSON_EXTRACT provides multi-value extraction. It’s suited to returning several fields from one JSON payload as typed SQL columns.

Both make JSON-derived values available to ordinary SQL, so they can be used alongside relational data in SELECT, JOIN, and WHERE clauses.

Trying it yourself

The examples above use table and column names you can adapt directly. For the full syntax, error handling options, and supported path expressions, see Exasol’s JSON functions documentation, the JSON_VALUE reference, and the JSON path expressions guide.

The takeaway

The order-events example illustrates the core idea. JSON arriving from an API doesn’t necessarily need a separate flattening job before developers can query the values it contains.

Use JSON_VALUE for one field. Use JSON_EXTRACT when you need several.

JSON doesn’t have to be flattened before it’s useful. You can extract the values you need and query them alongside relational data using SQL.