JSON Type

DuckDB supports json via the JSON logical type. The JSON logical type is interpreted as JSON, i.e., parsed, in JSON functions rather than interpreted as VARCHAR, i.e., a regular string (modulo the equality-comparison caveat at the bottom of this page). All JSON creation functions return values of this type.

We also allow any of DuckDB's types to be casted to JSON, and JSON to be casted 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