⌘+k ctrl+k
1.4 (stable)
Search Shortcut cmd + k | ctrl + k
JSON Type

DuckDB supports json via the JSON logical type. For example:

SELECT '[1, null, {"key": "value"}]'::JSON;

[1, null, {"key": "value"}]

Logically, the JSON type is similar to a VARCHAR, but with the restriction that it must be valid JSON. Physically, the data is stored as a VARCHAR.

For example, you can't parse invalid JSON:

SELECT 'unquoted'::JSON;

Conversion Error: Malformed JSON at byte 0 of input: unexpected character.  Input: "unquoted"

Instead, what you probably want here is SELECT '"quoted"'::JSON.

Since the data is stored physically as a VARCHAR, whitespace is significant:

SELECT '{ "a": 5 }'::JSON = '{"a":5}'::JSON;

false

Please not that whitespaces are kept in roundtrips:

SELECT '{  "a":5 }'::JSON::VARCHAR

{  "a":5 }

The order of keys in objects is significant:

 SELECT '{"a":1,"b":2}'::JSON = '{"b":2,"a":1}'::JSON;

false

Duplicate keys are allowed in JSON objects:

SELECT '{"a":1,"a":2}'::JSON;

{"a":1,"a":2}

We allow any of DuckDB's types to be cast to JSON, and JSON to be cast back to any of DuckDB's types, for example, to cast JSON to DuckDB's STRUCT type, run:

SELECT '{"duck": 42}'::JSON::STRUCT(duck INTEGER);

{'duck': 42}

And back:

SELECT {duck: 42}::JSON;

{"duck":42}

This works for our nested types as shown in the example, but also for non-nested types:

SELECT '2023-05-12'::DATE::JSON;

"2023-05-12"

The only exception to this behavior is the cast from VARCHAR to JSON, which does not alter the data, but instead parses and validates the contents of the VARCHAR as JSON.

About this page
© 2025 DuckDB Foundation, Amsterdam NL
Code of Conduct Trademark Use