DEV Community

Cover image for Day 83 of #100DaysOfClickHouse - JSONExtractString() Now Supports Native JSON in ClickHouse® 26.3
Kanishga Subramani
Kanishga Subramani

Posted on

Day 83 of #100DaysOfClickHouse - JSONExtractString() Now Supports Native JSON in ClickHouse® 26.3

#clickhouse #devops #database #analytics

Introduction

When the native JSON data type was introduced in ClickHouse®, it provided a more structured and efficient way to store semi-structured data. However, there was one small inconvenience that many users quickly noticed.

Functions such as JSONExtractString() worked only with JSON stored as a String, not with the native JSON data type itself. If you attempted to use JSONExtractString() directly on a JSON column, ClickHouse® returned an error.

With ClickHouse® 26.3, this limitation has been removed.

JSONExtractString() (along with the other JSONExtract* functions) now works directly with the native JSON data type, making it easier to migrate applications, write reusable SQL, and work with JSON regardless of how it is stored.

Let's explore what changed and why it matters.

The Situation Before ClickHouse® 26.3

Before version 26.3, JSONExtractString() expected its first argument to be a String containing valid JSON.

For example:

WITH '{"ClickHouse":{"version":"26.3"}}' AS s
SELECT
    toTypeName(s),
    JSONExtractString(s, 'ClickHouse', 'version');
Enter fullscreen mode Exit fullscreen mode

Output:

┌─toTypeName(s)─┬─JSONExtractString(...)─┐
│ String        │ 26.3                  │
└───────────────┴───────────────────────┘
Enter fullscreen mode Exit fullscreen mode

Everything worked because the input was a String.

But What About the Native JSON Type?

Suppose you stored exactly the same data as a native JSON value.

WITH '{"ClickHouse":{"version":"26.3"}}'::JSON AS s
SELECT
    toTypeName(s),
    JSONExtractString(s, 'ClickHouse', 'version');
Enter fullscreen mode Exit fullscreen mode

In versions before 26.3, ClickHouse® returned an error similar to:

Code: 43.
The first argument of function JSONExtractString
should be a string containing JSON,
illegal type: JSON
Enter fullscreen mode Exit fullscreen mode

This happened because the function only accepted the String data type. Even though the value contained JSON, the type checker rejected it before execution.

What's New in ClickHouse® 26.3?

Starting with ClickHouse® 26.3, the same query works without any modifications.

WITH '{"ClickHouse":{"version":"26.3"}}'::JSON AS s
SELECT
    toTypeName(s),
    JSONExtractString(s, 'ClickHouse', 'version');
Enter fullscreen mode Exit fullscreen mode

Output:

┌─toTypeName(s)─┬─JSONExtractString(...)─┐
│ JSON          │ 26.3                  │
└───────────────┴───────────────────────┘
Enter fullscreen mode Exit fullscreen mode

The function now accepts both:

  • String
  • JSON

as valid input types.

What Exactly Changed?

The important thing to understand is that the behavior of JSONExtractString() itself has not changed.

It still:

  • parses JSON,
  • navigates to the specified path,
  • returns the extracted value as a String.

The improvement is simply that the function now recognizes the native JSON type as a valid input, instead of only accepting a JSON-formatted String.

Why Is This Useful?

At first glance, this may seem like a minor quality-of-life improvement. In practice, it removes a common friction point when working with JSON.

1. One Query Works Everywhere

Previously, you often had to know whether a column was stored as:

String
Enter fullscreen mode Exit fullscreen mode

or

JSON
Enter fullscreen mode Exit fullscreen mode

before deciding how to query it.

Now, the same extraction query works for both storage types.

This makes SQL easier to reuse across different tables and schemas.

2. Easier Migration to the Native JSON Type

Many applications started with JSON stored as plain strings.

As teams migrate those columns to the native JSON type, they no longer need to rewrite every query that uses JSONExtractString().

Existing SQL continues to work after the migration.

3. Less Conditional Logic

Applications that generate SQL dynamically sometimes needed different query templates depending on the column type.

For example:

If String
    use JSONExtractString()

If JSON
    use another approach
Enter fullscreen mode Exit fullscreen mode

With ClickHouse® 26.3, that distinction is no longer necessary for the JSONExtract* family of functions.

4. Better Developer Experience

The new behavior is simply more intuitive.

If a value is stored as JSON, it's reasonable to expect JSON extraction functions to work on it directly.

ClickHouse® 26.3 aligns the function's behavior with that expectation.

A Practical Example

Imagine an events table:

CREATE TABLE events
(
    id UInt64,
    payload JSON
)
ENGINE = MergeTree
ORDER BY id;
Enter fullscreen mode Exit fullscreen mode

Example row:

{
    "user": {
        "name": "Alice",
        "country": "India"
    }
}
Enter fullscreen mode Exit fullscreen mode

Extracting the user's name is now straightforward:

SELECT
    JSONExtractString(payload, 'user', 'name')
FROM events;
Enter fullscreen mode Exit fullscreen mode

Before 26.3, this query would fail because payload was of type JSON.

In 26.3 and later, it works directly.

Does This Replace Native JSON Access?

No.

ClickHouse® already supports accessing fields from a native JSON value using dot notation.

For example:

SELECT payload.user.name
FROM events;
Enter fullscreen mode Exit fullscreen mode

The new JSONExtractString() support does not replace this capability. Instead, it provides another option that is especially useful when:

  • reusing existing queries written for JSON stored as strings,
  • using dynamic JSON paths,
  • maintaining applications that already rely on the JSONExtract* family of functions, or
  • migrating applications incrementally to the native JSON data type.

Best Practices

While JSONExtractString() now works with native JSON, choosing the right approach depends on your use case.

  • Use dot notation when you know the JSON structure at design time and want concise, readable SQL.
  • Use JSONExtract* functions when working with dynamic paths, reusable query templates, or applications that already depend on these functions.
  • If you're migrating from String to JSON, you can continue using your existing extraction queries without modification in ClickHouse® 26.3 and later.

This flexibility makes adopting the native JSON type significantly easier.

Final Thoughts

Not every release feature needs to be a major performance breakthrough to improve day-to-day development.

The enhancement to JSONExtractString() in ClickHouse® 26.3 is a small but meaningful improvement. By allowing the function to operate directly on the native JSON data type, ClickHouse® removes an unnecessary limitation and makes JSON querying more consistent.

If your applications are adopting the native JSON data type—or if you're gradually migrating from JSON stored as strings—this change means your existing JSONExtract* queries are far more likely to continue working without modification.

It's a small feature, but one that contributes to a smoother and more intuitive developer experience when working with JSON in ClickHouse®.

References

Top comments (0)