Parse and Minify JSON with DuckDB’s JSON() Function

One of the many JSON functions that DuckDB provides is the json() function, which parses and minifies JSON values. We pass the JSON data to the function, and it parses and minifies the JSON, then returns the result.

Syntax

The syntax is very simple:

json(json)

So it accepts just one argument; the JSON value we want parsed and minified.

Example

Here’s a simple example to demonstrate:

SET VARIABLE json_doc = '{
    "name": "Flex Jackson",
    "age": 32,
    "contact": {
        "email": "[email protected]",
        "phone": "555-1234"
    },
    "preferences": {
        "theme": "dark",
        "notifications": true
    },
    "tags": ["developer", "premium"]
}';
SELECT json(getvariable('json_doc')) AS minified_json;

Output:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                 minified_json                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"name":"Flex Jackson","age":32,"contact":{"email":"[email protected]","phone":"555-1234"},"preferences":{"theme":"dark","notifications":true},"tags":["developer","premium"]} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

If I run it through the typeof() function, I can see that the result of json() is returned as JSON:

SET VARIABLE json_doc = '{
    "name": "Flex Jackson",
    "age": 32,
    "contact": {
        "email": "[email protected]",
        "phone": "555-1234"
    },
    "preferences": {
        "theme": "dark",
        "notifications": true
    },
    "tags": ["developer", "premium"]
}';
SELECT typeof(json(getvariable('json_doc'))) AS return_type;

Output:

+-------------+
| return_type |
+-------------+
| JSON        |
+-------------+

Passing Other Data Types

Passing other data types result in the value being returned as JSON:

SELECT 
    typeof(json('Cat')) AS "string",
    typeof(json(date '2020-01-01')) AS "date",
    typeof(json(interval '3 days 23 hours')) AS "interval",
    typeof(json(list_value(1, 2, 3))) AS "list",
    typeof(json(struct_pack(name := 'Jay', age := 57))) AS "struct";

Result:

+--------+------+----------+------+--------+
| string | date | interval | list | struct |
+--------+------+----------+------+--------+
| JSON   | JSON | JSON     | JSON | JSON   |
+--------+------+----------+------+--------+

But passing a numeric literal results in an error:

SELECT json(22);

Result:

Binder Error:
No function matches the given name and argument types 'json_extract(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	json_extract(VARCHAR, BIGINT) -> JSON
	json_extract(VARCHAR, VARCHAR) -> JSON
	json_extract(VARCHAR, VARCHAR[]) -> JSON[]
	json_extract(JSON, BIGINT) -> JSON
	json_extract(JSON, VARCHAR) -> JSON
	json_extract(JSON, VARCHAR[]) -> JSON[]


LINE 1: SELECT json(22);
               ^

But if the number is surrounded in quotes, then it’s fine:

SELECT typeof(json('22'));

Result:

+----------------------+
| typeof("json"('22')) |
+----------------------+
| JSON                 |
+----------------------+

And if we cast it as an integer, it works without error and is returned as JSON:

SELECT 
    json(cast(22 as int)) AS converted,
    typeof(json(cast(22 as int))) AS return_type;

Output:

+-----------+-------------+
| converted | return_type |
+-----------+-------------+
| 22        | JSON        |
+-----------+-------------+