Using JSON_TRANSFORM_STRICT() to Perform Strict JSON Transformations in DuckDB

by

DuckDB provides us with a json_transform_strict() function that works just like the json_transform() function, except that it throws an error when type casting fails. The json_transform() function on the other hand, returns NULL for the respective field in such cases.

Syntax

The basic syntax is as follows:

json_transform_strict(json, structure)

Where json is the JSON document and structure is the structure you want it transformed to.

Example

Suppose we create and populate a table that contains JSON data:

CREATE TABLE users (data JSON);
INSERT INTO users VALUES ('{"name": "Jake Sandilands", "age": 30, "is_active": true}');

We can use json_transform_strict() to convert this JSON into a STRUCT with typed columns:

SELECT
    json_transform_strict(data, '{"name": "VARCHAR", "age": "INTEGER", "is_active": "BOOLEAN"}') AS user_data
FROM users;

Result:

+---------------------------------------------------------+
|                        user_data                        |
+---------------------------------------------------------+
| {'name': Jake Sandilands, 'age': 30, 'is_active': true} |
+---------------------------------------------------------+

The output is a STRUCT where we can access each field by its key.

So far, so good. In this example all fields in the JSON document were able to be converted to the data types I specified.

But what happens when a field can’t be converted? Let’s find out:

SELECT
    json_transform_strict(data, '{"name": "INTEGER", "age": "INTEGER", "is_active": "BOOLEAN"}') AS user_data
FROM users;

Result:

Invalid Input Error:
Failed to cast value to numerical: "Jake Sandilands"

We got an error, due to the name field not being able to be converted to an INTEGER.

Compared to json_transform()

Let’s change the above example to use the json_transform() function:

SELECT
    json_transform(data, '{"name": "INTEGER", "age": "INTEGER", "is_active": "BOOLEAN"}') AS user_data
FROM users;

Result:

+----------------------------------------------+
|                  user_data                   |
+----------------------------------------------+
| {'name': NULL, 'age': 30, 'is_active': true} |
+----------------------------------------------+

This time there was no error. Instead, we got our STRUCT, but with NULL in the field that couldn’t be converted.

An Alias: The from_json_strict() Function

DuckDB also provides a from_json_strict() function, which is an alias for json_transform_strict(). Therefore, we can replace the earlier example with from_json_strict() to achieve the same error:

SELECT
    from_json_strict(data, '{"name": "INTEGER", "age": "INTEGER", "is_active": "BOOLEAN"}') AS user_data
FROM users;

Result:

Invalid Input Error:
Failed to cast value to numerical: "Jake Sandilands"

There’s also a from_json() function which is an alias for json_transform().