-- SQL/JSON constructors SELECT JSON_OBJECT(); SELECT JSON_OBJECT(RETURNING json); SELECT JSON_OBJECT(RETURNING jsonb); SELECT JSON_OBJECT(RETURNING text FORMAT JSON); SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF8); SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON); SELECT JSON_OBJECT('foo': 1, 'bar': 'baz'); SELECT JSON_OBJECT('foo' VALUE 1, 'bar' VALUE 'baz'); SELECT JSON_OBJECT('a': 1 ABSENT ON NULL); SELECT JSON_OBJECT('a': 1 NULL ON NULL); SELECT JSON_OBJECT('a': 1, 'a': 2 WITH UNIQUE KEYS); SELECT JSON_OBJECT('a': 1, 'a': 2 WITHOUT UNIQUE KEYS); SELECT JSON_OBJECT('a': 1, 'b': 2 ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb); SELECT JSON_OBJECT('a': '{"b": 1}' FORMAT JSON); SELECT JSON_OBJECT('a': '{"b": 1}' FORMAT JSON RETURNING jsonb); SELECT JSON_ARRAY(); SELECT JSON_ARRAY(RETURNING jsonb); SELECT JSON_ARRAY(1, 2, 'a'); SELECT JSON_ARRAY(1, NULL, 2 NULL ON NULL); SELECT JSON_ARRAY(1, NULL, 2 ABSENT ON NULL); SELECT JSON_ARRAY('{"a": 1}' FORMAT JSON, 2 RETURNING jsonb); SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i)); SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2)) foo(i) RETURNING jsonb); SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1)) foo(i) ORDER BY i); SELECT JSON_ARRAY(SELECT i FROM (VALUES ('[1]')) foo(i) FORMAT JSON RETURNING text); SELECT JSON('{"a": 1}'); SELECT JSON('{"a": 1}' WITH UNIQUE KEYS); SELECT JSON('{"a": 1}' WITHOUT UNIQUE KEYS); SELECT JSON('{"a": 1}'::bytea FORMAT JSON ENCODING UTF8); SELECT JSON_SCALAR(1); SELECT JSON_SCALAR('text'); SELECT JSON_SCALAR(NULL::int); SELECT JSON_SERIALIZE('{"a": 1}'); SELECT JSON_SERIALIZE('{"a": 1}' FORMAT JSON); SELECT JSON_SERIALIZE('{"a": 1}'::jsonb RETURNING bytea); SELECT JSON_SERIALIZE('{"a": 1}' RETURNING bytea FORMAT JSON ENCODING UTF8); -- SQL/JSON aggregates SELECT JSON_OBJECTAGG(k: v) FROM t; SELECT JSON_OBJECTAGG(k VALUE v) FROM t; SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL) FROM t; SELECT JSON_OBJECTAGG(k: v NULL ON NULL WITH UNIQUE KEYS RETURNING jsonb) FROM t; SELECT JSON_OBJECTAGG(k: v FORMAT JSON) FROM t; SELECT JSON_OBJECTAGG(k: v) FILTER (WHERE k IS NOT NULL) FROM t; SELECT JSON_OBJECTAGG(k: v) OVER (PARTITION BY g) FROM t; SELECT JSON_OBJECTAGG(k: v) FILTER (WHERE k IS NOT NULL) OVER (ORDER BY k ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t; SELECT JSON_ARRAYAGG(v) FROM t; SELECT JSON_ARRAYAGG(v ORDER BY v DESC) FROM t; SELECT JSON_ARRAYAGG(v NULL ON NULL) FROM t; SELECT JSON_ARRAYAGG(v ABSENT ON NULL RETURNING jsonb) FROM t; SELECT JSON_ARRAYAGG(v FORMAT JSON ORDER BY v ABSENT ON NULL RETURNING text) FROM t; SELECT JSON_ARRAYAGG(v) FILTER (WHERE v > 0) OVER w FROM t WINDOW w AS (PARTITION BY g); -- IS JSON predicates SELECT js IS JSON FROM t; SELECT js IS NOT JSON FROM t; SELECT js IS JSON VALUE FROM t; SELECT js IS JSON OBJECT FROM t; SELECT js IS JSON ARRAY FROM t; SELECT js IS JSON SCALAR FROM t; SELECT js IS JSON WITH UNIQUE KEYS FROM t; SELECT js IS JSON OBJECT WITHOUT UNIQUE KEYS FROM t; SELECT js IS NOT JSON ARRAY WITH UNIQUE FROM t; SELECT CASE WHEN js IS JSON OBJECT THEN 'o' WHEN js IS JSON ARRAY THEN 'a' END FROM t;