How to Parse JSON in ClickHouse

How do I use JSONExtract to parse JSON columns in ClickHouse?

Parsing JSON in ClickHouse means using built-in JSON* functions to extract, transform, and query values from JSON-formatted strings stored in table columns.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why do I need JSON parsing in ClickHouse?

Storing semi-structured data like API responses or event payloads in VARCHAR columns is common. JSON functions let you extract pieces of those blobs into typed fields for filtering, joins, and analytics.

Which functions parse JSON?

Core helpers are JSONExtract(…), JSONExtractString, JSONExtractInt, JSONExtractFloat, JSONExtractKeysAndValues, and the ANSI-style JSON_VALUE. They all accept a JSON string and a JSONPath expression.

What is the basic workflow?

1. Identify the JSON column. 2. Choose a JSONExtract variant matching the desired ClickHouse data type. 3. Provide the key path (e.g., '$.payment.method'). 4. Alias the result for readability.

How do I query an Orders table with JSON?

Suppose Orders has a metadata column storing extra details. To list orders paid with PayPal:

SELECT id, JSONExtractString(metadata, '$.payment.method') AS pay_method FROM Orders WHERE JSONExtractString(metadata, '$.payment.method') = 'PayPal';

Can I cast JSON to arrays or maps?

Yes. Use JSONExtractArrayRaw or JSONExtractKeysAndValues, then apply arrayJoin or map functions for deeper analysis.

How do I avoid repeated extraction?

Create a materialized view that copies extracted fields into dedicated columns. This speeds up filters and aggregates while keeping raw JSON for audit.

Best practices for JSON parsing

• Store JSON as String not LowCardinality(String) to prevent dictionary bloat.
• Index frequently used keys via skip indexes or projections.
• Validate JSON upstream; ClickHouse treats malformed JSON as empty.

Why How to Parse JSON in ClickHouse is important

How to Parse JSON in ClickHouse Example Usage


-- Total PayPal revenue in 2024
SELECT sum(total_amount) AS paypal_revenue
FROM Orders
WHERE order_date >= '2024-01-01'
  AND JSONExtractString(metadata, '$.payment.method') = 'PayPal';

How to Parse JSON in ClickHouse Syntax


-- Extract a string value
SELECT JSONExtractString(metadata, '$.payment.method')            AS pay_method
FROM Orders;

-- Extract numeric values and cast
SELECT id,
       JSONExtractInt(metadata,   '$.shipping.days')              AS ship_days,
       JSONExtractFloat(metadata, '$.discount.amount')            AS discount
FROM Orders;

-- Filter by nested key
SELECT *
FROM Orders
WHERE JSONExtractString(metadata, '$.customer.tier') = 'gold';

-- Extract an array of product IDs from OrderItems JSON blob
SELECT order_id,
       JSONExtractArrayRaw(items_json, '$.products') AS product_ids
FROM OrderItems;

Common Mistakes

Using JSONExtractString on numeric fields. This returns NULL for numbers. Fix: pick the correct typed variant, e.g., JSONExtractInt or cast the result.
Filtering on JSONExtract in large tables without indexes. This forces full scans. Fix: add a projection or skip index on the extracted key or persist it in a materialized view.