JSON Type

Goose 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 note 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 Goose's types to be cast to JSON, and JSON to be cast back to any of Goose's types, for example, to cast JSON to Goose'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.