JSON Type

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

SQL

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

 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:

SQL

SELECT 'unquoted'::JSON;

db error: ERROR: Malformed JSON at byte 0 of input: unexpected character, expected a JSON value.  Input: "unquoted"

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

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

SQL

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

 ?column?
----------
 f

Please note that whitespaces are kept in roundtrips:

SQL

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

 text
------------
 {  "a":5 }

The order of keys in objects is significant:

SQL

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

 ?column?
----------
 f

Duplicate keys are allowed in JSON objects:

SQL

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

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

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

SQL

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

 struct(mercury integer)
-------------------------
 (42)

And back:

SQL

SELECT {mercury: 42}::JSON;

 struct_pack
----------------
 {"mercury":42}

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

SQL

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

 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.