-- sift.json_build_object writes the bytes of json_build_object, for every type of argument, key and error; its type lookups are made once per call
-- site and made again after a change of a type or a cast
CREATE EXTENSION pg_sift;
SET TimeZone = 'UTC';
SET DateStyle = 'ISO, MDY';
SET extra_float_digits = 1;
CREATE TYPE jb_pair AS (a int, b text);
CREATE TYPE jb_nest AS (p jb_pair, q int[], r jb_pair[]);
CREATE TYPE jb_lvl3 AS ENUM ('lo', 'ok', 'hi');
CREATE DOMAIN jb_posint AS int CHECK (VALUE > 0);
CREATE DOMAIN jb_label AS text;
CREATE DOMAIN jb_doc AS json;
CREATE TYPE jb_opaque AS ENUM ('o1', 'o2');
CREATE FUNCTION jb_opaque_json(jb_opaque) RETURNS json LANGUAGE sql IMMUTABLE AS $$ SELECT json_build_object('opaque', $1::text) $$;
CREATE CAST (jb_opaque AS json) WITH FUNCTION jb_opaque_json(jb_opaque);
-- what each of the two functions gives for an argument list: the text, or the error with its code, message and hint
CREATE FUNCTION jb_run(fn text, args text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text; st text; m text; h text;
BEGIN
EXECUTE format('SELECT %s(%s)::text', fn, args) INTO r;
RETURN coalesce(r, 'NULL');
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS st = RETURNED_SQLSTATE, m = MESSAGE_TEXT, h = PG_EXCEPTION_HINT;
RETURN format('error %s %s [%s]', st, m, h);
END $$;
CREATE FUNCTION jb_same(args text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE a text := jb_run('json_build_object', args); b text := jb_run('sift.json_build_object', args);
BEGIN
RETURN CASE WHEN a = b THEN 'same ' || a ELSE format('DIFFERENT core %s sift %s', a, b) END;
END $$;
SELECT jb_same(a) AS result FROM unnest(ARRAY[
-- the arity and the keys
$$'a', 1$$, $$'a', 1, 'b', 2, 'a', 3$$, $$'a'$$, $$'a', 1, 'b'$$, $$NULL, 1$$, $$NULL::text, 1$$, $$'a', NULL$$, $$'a', NULL::int, 'b', NULL::json$$,
$$VARIADIC ARRAY['a', '1', 'b', '2']$$, $$VARIADIC NULL::text[]$$, $$VARIADIC ARRAY[]::text[]$$, $$VARIADIC ARRAY[1, 2, 3]$$, $$VARIADIC '{}'::int[]$$,
-- text, escapes, unicode
$$'k', 'plain'$$, $$'k', 'quo"te'$$, $$'k', E'back\\slash'$$, $$'k', E'tab\tnl\ncr\r\bff\f'$$, $$'k', E'\u0001\u001f\u007f'$$, $$'k', 'ünï©ödé ✓ 😀'$$, $$'k', ''$$, $$'k', ''$$,
$$'k', 'v'::varchar$$, $$'k', 'v'::char(5)$$, $$'k', 'v'::name$$, $$'k', 'v'::"char"$$, $$'k', '\x00ff'::bytea$$, $$'k', 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid$$,
$$'k"ey', 1, E'k\\ey', 2, 'ké', 3, '', 4$$,
-- numbers
$$'k', 1::int2$$, $$'k', -1::int4$$, $$'k', 9223372036854775807::int8$$, $$'k', 1.5::float4$$, $$'k', 0.1::float8$$, $$'k', -0.0::float8$$, $$'k', 1e100::float8$$,
$$'k', 'NaN'::float8$$, $$'k', 'Infinity'::float8$$, $$'k', '-Infinity'::float4$$, $$'k', 1.50::numeric$$, $$'k', -0::numeric$$, $$'k', 'NaN'::numeric$$,
$$'k', 123456789012345678901234567890.123456789::numeric$$, $$'k', 1e-30::numeric$$, $$'k', 12.5::money$$, $$'k', 5::oid$$, $$'k', 'pg_class'::regclass$$,
-- booleans, dates and times
$$'k', true, 'l', false$$, $$'k', DATE '2026-02-03'$$, $$'k', DATE 'infinity'$$, $$'k', DATE '0044-03-15 BC'$$, $$'k', TIMESTAMP '2026-02-03 04:05:06.789'$$,
$$'k', TIMESTAMPTZ '2026-02-03 04:05:06.789+05:30'$$, $$'k', TIMESTAMPTZ '-infinity'$$, $$'k', TIME '04:05:06'$$, $$'k', TIMETZ '04:05:06+02'$$, $$'k', INTERVAL '1 year 2 mons 3 days 04:05:06'$$,
-- json, jsonb, arrays, composites, records
$$'k', '{"a" : [1, 2]}'::json$$, $$'k', '{"a" : [1, 2]}'::jsonb$$, $$'k', 'null'::json$$, $$'k', '"s"'::jsonb$$, $$'k', jb_doc '{"x" : 1}'$$,
$$'k', ARRAY[1, 2, NULL]$$, $$'k', ARRAY['a', 'b"c', NULL]$$, $$'k', ARRAY[[1, 2], [3, 4]]$$, $$'k', '{}'::int[]$$, $$'k', ARRAY[DATE '2026-01-01']$$, $$'k', ARRAY['{"a": 1}'::json]$$,
$$'k', ROW(1, 'x')$$, $$'k', ROW(1, 'x')::jb_pair$$, $$'k', ROW(NULL, NULL)::jb_pair$$, $$'k', ROW(ROW(1, 'x'), ARRAY[1, 2], ARRAY[ROW(2, 'y')::jb_pair])::jb_nest$$,
$$'k', ARRAY[ROW(1, 'x')::jb_pair, NULL]$$, $$'k', (SELECT t FROM (VALUES (1, 'a')) t (x, y))$$,
-- domains, enums, casts, other types
$$'k', 5::jb_posint$$, $$'k', 'lab"el'::jb_label$$, $$'k', 'ok'::jb_lvl3$$, $$'k', ARRAY['lo', 'hi']::jb_lvl3[]$$, $$'k', 'o1'::jb_opaque$$, $$'k', ARRAY['o1'::jb_opaque]$$,
$$'k', '1.2.3.4/24'::inet$$, $$'k', '10.0.0.0/8'::cidr$$, $$'k', '08:00:2b:01:02:03'::macaddr$$, $$'k', B'1011'::varbit$$, $$'k', int4range(1, 5)$$, $$'k', daterange('2026-01-01', NULL)$$,
$$'k', 'a:1 b:2'::tsvector$$, $$'k', '1'::xml$$, $$'k', point(1, 2)$$, $$'k', '1 2 3'::int2vector$$,
-- keys that are not text
$$1, 'v'$$, $$1.5::numeric, 'v'$$, $$true, 'v'$$, $$DATE '2026-02-03', 'v'$$, $$TIMESTAMPTZ '2026-02-03 04:05:06+00', 'v'$$, $$5::jb_posint, 'v'$$, $$'ok'::jb_lvl3, 'v'$$,
$$ARRAY[1], 'v'$$, $$ROW(1, 'x'), 'v'$$, $$'{"a": 1}'::json, 'v'$$, $$'{"a": 1}'::jsonb, 'v'$$, $$'o1'::jb_opaque, 'v'$$, $$'NaN'::numeric, 'v'$$, $$'NaN'::float8, 'v'$$,
-- unknown literals and nesting
$$'a', 'b', 'c', NULL, 'd', 'e'$$, $$'a', json_build_object('b', json_build_object('c', 1))$$, $$'a', sift.json_build_object('b', ARRAY[sift.json_build_object('c', 1)])$$
]) a;
result
------------------------------------------------------------------------------------------------------------------------------------------------------
same {"a" : 1}
same {"a" : 1, "b" : 2, "a" : 3}
same error 22023 argument list must have even number of elements [The arguments of json_build_object() must consist of alternating keys and values.]
same error 22023 argument list must have even number of elements [The arguments of json_build_object() must consist of alternating keys and values.]
same error 22004 null value not allowed for object key []
same error 22004 null value not allowed for object key []
same {"a" : null}
same {"a" : null, "b" : null}
same {"a" : "1", "b" : "2"}
same NULL
same {}
same error 22023 argument list must have even number of elements [The arguments of json_build_object() must consist of alternating keys and values.]
same {}
same {"k" : "plain"}
same {"k" : "quo\"te"}
same {"k" : "back\\slash"}
same {"k" : "tab\tnl\ncr\r\bff\f"}
same {"k" : "\u0001\u001f\x7F"}
same {"k" : "ünï©ödé ✓ 😀"}
same {"k" : ""}
same {"k" : ""}
same {"k" : "v"}
same {"k" : "v "}
same {"k" : "v"}
same {"k" : "v"}
same {"k" : "\\x00ff"}
same {"k" : "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"}
same {"k\"ey" : 1, "k\\ey" : 2, "ké" : 3, "" : 4}
same {"k" : 1}
same {"k" : -1}
same {"k" : 9223372036854775807}
same {"k" : 1.5}
same {"k" : 0.1}
same {"k" : -0}
same {"k" : 1e+100}
same {"k" : "NaN"}
same {"k" : "Infinity"}
same {"k" : "-Infinity"}
same {"k" : 1.50}
same {"k" : 0}
same {"k" : "NaN"}
same {"k" : 123456789012345678901234567890.123456789}
same {"k" : 0.000000000000000000000000000001}
same {"k" : "$12.50"}
same {"k" : "5"}
same {"k" : "pg_class"}
same {"k" : true, "l" : false}
same {"k" : "2026-02-03"}
same {"k" : "infinity"}
same {"k" : "0044-03-15 BC"}
same {"k" : "2026-02-03T04:05:06.789"}
same {"k" : "2026-02-02T22:35:06.789+00:00"}
same {"k" : "-infinity"}
same {"k" : "04:05:06"}
same {"k" : "04:05:06+02"}
same {"k" : "@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs"}
same {"k" : {"a" : [1, 2]}}
same {"k" : {"a": [1, 2]}}
same {"k" : null}
same {"k" : "s"}
same {"k" : {"x" : 1}}
same {"k" : [1,2,null]}
same {"k" : ["a","b\"c",null]}
same {"k" : [[1,2],[3,4]]}
same {"k" : []}
same {"k" : ["2026-01-01"]}
same {"k" : [{"a": 1}]}
same {"k" : {"f1":1,"f2":"x"}}
same {"k" : {"a":1,"b":"x"}}
same {"k" : {"a":null,"b":null}}
same {"k" : {"p":{"a":1,"b":"x"},"q":[1,2],"r":[{"a":2,"b":"y"}]}}
same {"k" : [{"a":1,"b":"x"},null]}
same {"k" : {"x":1,"y":"a"}}
same {"k" : 5}
same {"k" : "lab\"el"}
same {"k" : "ok"}
same {"k" : ["lo","hi"]}
same {"k" : {"opaque" : "o1"}}
same {"k" : [{"opaque" : "o1"}]}
same {"k" : "1.2.3.4/24"}
same {"k" : "10.0.0.0/8"}
same {"k" : "08:00:2b:01:02:03"}
same {"k" : "1011"}
same {"k" : "[1,5)"}
same {"k" : "[2026-01-01,)"}
same {"k" : "'a':1 'b':2"}
same {"k" : "1"}
same {"k" : "(1,2)"}
same {"k" : [1,2,3]}
same {"1" : "v"}
same {"1.5" : "v"}
same {"true" : "v"}
same {"2026-02-03" : "v"}
same {"2026-02-03T04:05:06+00:00" : "v"}
same {"5" : "v"}
same {"ok" : "v"}
same error 22023 key value must be scalar, not array, composite, or json []
same error 22023 key value must be scalar, not array, composite, or json []
same error 22023 key value must be scalar, not array, composite, or json []
same error 22023 key value must be scalar, not array, composite, or json []
same error 22023 key value must be scalar, not array, composite, or json []
same {"NaN" : "v"}
same {"NaN" : "v"}
same {"a" : "b", "c" : null, "d" : "e"}
same {"a" : {"b" : {"c" : 1}}}
same {"a" : {"b" : [{"c" : 1}]}}
(106 rows)
-- another TimeZone and DateStyle
SET TimeZone = 'Asia/Kolkata';
SET DateStyle = 'German, DMY';
SELECT jb_same(a) AS result FROM unnest(ARRAY[
$$'k', TIMESTAMPTZ '2026-02-03 04:05:06.789+00', 'l', TIMESTAMP '2026-02-03 04:05:06', 'm', DATE '2026-02-03'$$,
$$'k', ARRAY[TIMESTAMPTZ '2026-02-03 04:05:06+00']$$, $$'k', ROW(DATE '2026-02-03', INTERVAL '3 days')$$, $$TIMESTAMPTZ '2026-02-03 04:05:06+00', 1$$
]) a;
result
-----------------------------------------------------------------------------------------------
same {"k" : "2026-02-03T09:35:06.789+05:30", "l" : "2026-02-03T04:05:06", "m" : "2026-02-03"}
same {"k" : ["2026-02-03T09:35:06+05:30"]}
same {"k" : {"f1":"2026-02-03","f2":"@ 3 days"}}
same {"2026-02-03T09:35:06+05:30" : 1}
(4 rows)
RESET TimeZone;
RESET DateStyle;
-- rows: the lookups are made once for a column of one type, and again when the type of an argument changes between calls of one call site
CREATE TABLE jb_rows (id int, t text, n numeric, d date, j jsonb, anyv text);
INSERT INTO jb_rows SELECT i, CASE WHEN i % 7 = 0 THEN NULL ELSE 'v"' || i END, i / 3.0, DATE '2026-01-01' + i, jsonb_build_object('i', i), CASE WHEN i % 2 = 0 THEN i::text ELSE NULL END FROM generate_series(1, 200) i;
SELECT count(*) AS rows, bool_and(json_build_object('id', id, 't', t, 'n', n, 'd', d, 'j', j, 'r', ROW(id, t)::jb_pair)::text
= sift.json_build_object('id', id, 't', t, 'n', n, 'd', d, 'j', j, 'r', ROW(id, t)::jb_pair)::text) AS same FROM jb_rows;
rows | same
------+------
200 | t
(1 row)
SELECT count(*) AS groups, bool_and(a = b) AS same FROM (
SELECT json_agg(json_build_object('id', id, 't', t))::text AS a, json_agg(sift.json_build_object('id', id, 't', t))::text AS b FROM jb_rows GROUP BY id % 5) s;
groups | same
--------+------
5 | t
(1 row)
-- one call site, unknown literals (read as C strings) and a VARIADIC array, for every row
SELECT count(*) AS rows, bool_and(json_build_object('k"e\y', E'v"é\n\t', 'n', id, 'u', 'x', 't', t)::text
= sift.json_build_object('k"e\y', E'v"é\n\t', 'n', id, 'u', 'x', 't', t)::text) AS same_literals,
bool_and(json_build_object(VARIADIC ARRAY['a', t, 'b', anyv])::text = sift.json_build_object(VARIADIC ARRAY['a', t, 'b', anyv])::text) AS same_variadic FROM jb_rows;
rows | same_literals | same_variadic
------+---------------+---------------
200 | t | t
(1 row)
-- one call site, polymorphic argument of different types in turn
CREATE FUNCTION jb_poly(anyelement) RETURNS TABLE (core text, sift text) LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY SELECT json_build_object('v', $1)::text, sift.json_build_object('v', $1)::text;
END $$;
SELECT core = sift AS same, sift AS result FROM (SELECT (jb_poly(v)).* FROM (VALUES (1), (2)) t (v)) a;
same | result
------+-----------
t | {"v" : 1}
t | {"v" : 2}
(2 rows)
SELECT core = sift AS same, sift AS result FROM (SELECT (jb_poly(v)).* FROM (VALUES ('a'::text), ('b"')) t (v)) a;
same | result
------+---------------
t | {"v" : "a"}
t | {"v" : "b\""}
(2 rows)
SELECT core = sift AS same, sift AS result FROM jb_poly(DATE '2026-02-03') a;
same | result
------+----------------------
t | {"v" : "2026-02-03"}
(1 row)
SELECT core = sift AS same, sift AS result FROM jb_poly(ARRAY[1, 2]) a;
same | result
------+---------------
t | {"v" : [1,2]}
(1 row)
-- a prepared statement keeps its call site: a cast created or dropped under it is met on the next execution
CREATE TYPE jb_late AS ENUM ('x', 'y');
PREPARE jb_p(jb_late) AS SELECT json_build_object('v', $1)::text = sift.json_build_object('v', $1)::text AS same, sift.json_build_object('v', $1) AS result;
EXECUTE jb_p('x');
same | result
------+-------------
t | {"v" : "x"}
(1 row)
CREATE FUNCTION jb_late_json(jb_late) RETURNS json LANGUAGE sql IMMUTABLE AS $$ SELECT json_build_object('late', $1::text) $$;
CREATE CAST (jb_late AS json) WITH FUNCTION jb_late_json(jb_late);
EXECUTE jb_p('x');
same | result
------+------------------------
t | {"v" : {"late" : "x"}}
(1 row)
DROP CAST (jb_late AS json);
EXECUTE jb_p('x');
same | result
------+-------------
t | {"v" : "x"}
(1 row)
CREATE DOMAIN jb_dom AS text;
PREPARE jb_q(jb_dom) AS SELECT json_build_object('v', $1)::text = sift.json_build_object('v', $1)::text AS same, sift.json_build_object('v', $1) AS result;
EXECUTE jb_q('a');
same | result
------+-------------
t | {"v" : "a"}
(1 row)
ALTER TYPE jb_lvl3 ADD VALUE 'ecstatic';
SELECT jb_same($$'k', 'ecstatic'::jb_lvl3$$) AS result;
result
-------------------------
same {"k" : "ecstatic"}
(1 row)
DEALLOCATE jb_p;
DEALLOCATE jb_q;
-- the cast of a type to json shows in the text, and is dropped from it again
-- the planner treats it as a function of the session: stable, parallel safe, not strict
SELECT pronamespace::regnamespace AS schema, provolatile, proparallel, proisstrict, prorettype::regtype, provariadic::regtype
FROM pg_proc WHERE proname = 'json_build_object' AND pronamespace IN ('sift'::regnamespace, 'pg_catalog'::regnamespace) AND pronargs = 1 ORDER BY pronamespace::regnamespace::text;
schema | provolatile | proparallel | proisstrict | prorettype | provariadic
------------+-------------+-------------+-------------+------------+-------------
pg_catalog | s | s | f | json | "any"
sift | s | s | f | json | "any"
(2 rows)
-- one call site, parallel workers
SET max_parallel_workers_per_gather = 2; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_table_scan_size = 0;
SELECT count(*) AS rows, bool_and(json_build_object('id', id, 't', t)::text = sift.json_build_object('id', id, 't', t)::text) AS same FROM jb_rows;
rows | same
------+------
200 | t
(1 row)
RESET max_parallel_workers_per_gather; RESET parallel_setup_cost; RESET parallel_tuple_cost; RESET min_parallel_table_scan_size;
-- one call site, many rows: integers, float8 at every extra_float_digits (NaN, the infinities, -0 and an exponent among them), NULLs, json values and constant keys, written as the core writes them
CREATE TABLE jb_num AS SELECT g, CASE g % 7 WHEN 0 THEN 'NaN'::float8 WHEN 1 THEN 'Infinity' WHEN 2 THEN '-Infinity' WHEN 3 THEN -0.0 WHEN 4 THEN 1e100 ELSE g / 7.0 END AS f,
CASE WHEN g = 5 THEN -9223372036854775808 ELSE g::int8 * 40000000000000 END AS b, (g - 100)::int2 AS s, g / 3.0 AS n, CASE WHEN g % 11 = 0 THEN NULL ELSE json_build_object('x', g)::json END AS j
FROM generate_series(1, 200) g;
SET extra_float_digits = 0;
SELECT count(*) AS rows, bool_and(json_build_object('g', g, 'f', f, 'b', b, 's', s, 'n', n, 'j', j, 'z', NULL::int, 'l', g::text)::text = sift.json_build_object('g', g, 'f', f, 'b', b, 's', s, 'n', n, 'j', j, 'z', NULL::int, 'l', g::text)::text) AS same FROM jb_num;
rows | same
------+------
200 | t
(1 row)
SET extra_float_digits = 1;
SELECT count(*) AS rows, bool_and(json_build_object('g', g, 'f', f, 'b', b, 's', s, 'n', n, 'j', j, 'z', NULL::int, 'l', g::text)::text = sift.json_build_object('g', g, 'f', f, 'b', b, 's', s, 'n', n, 'j', j, 'z', NULL::int, 'l', g::text)::text) AS same FROM jb_num;
rows | same
------+------
200 | t
(1 row)
SET extra_float_digits = 3;
SELECT count(*) AS rows, bool_and(json_build_object('g', g, 'f', f, 'b', b, 's', s, 'n', n, 'j', j, 'z', NULL::int, 'l', g::text)::text = sift.json_build_object('g', g, 'f', f, 'b', b, 's', s, 'n', n, 'j', j, 'z', NULL::int, 'l', g::text)::text) AS same FROM jb_num;
rows | same
------+------
200 | t
(1 row)
SET extra_float_digits = 1;
-- a key that is a parameter is the same for the executions of one plan and not for the next: the key of a call is written again, and a NULL key is an error after good calls
PREPARE jb_k(text, int) AS SELECT sift.json_build_object($1, $2, 'b', $2)::text = json_build_object($1, $2, 'b', $2)::text AS same, sift.json_build_object($1, $2, 'b', $2) AS result;
EXECUTE jb_k('a', 1);
same | result
------+--------------------
t | {"a" : 1, "b" : 1}
(1 row)
EXECUTE jb_k('quo"te', 2);
same | result
------+--------------------------
t | {"quo\"te" : 2, "b" : 2}
(1 row)
EXECUTE jb_k('a', 3);
same | result
------+--------------------
t | {"a" : 3, "b" : 3}
(1 row)
EXECUTE jb_k(NULL, 4);
ERROR: null value not allowed for object key
DEALLOCATE jb_k;
DROP TABLE jb_num;
-- sift before pg_catalog in the search_path takes the name for every call
SET search_path = sift, pg_catalog, public;
SELECT json_build_object('a', 1, 'b', ARRAY['x']) AS result;
result
------------------------
{"a" : 1, "b" : ["x"]}
(1 row)
SELECT to_regprocedure('json_build_object("any")') = to_regprocedure('sift.json_build_object("any")') AS takes_the_name;
takes_the_name
----------------
t
(1 row)
RESET search_path;
DROP TABLE jb_rows;
DROP FUNCTION jb_poly(anyelement), jb_same(text), jb_run(text, text);
DROP CAST (jb_opaque AS json);
DROP FUNCTION jb_opaque_json(jb_opaque), jb_late_json(jb_late);
DROP TYPE jb_nest, jb_pair CASCADE;
DROP TYPE jb_opaque, jb_late;
DROP DOMAIN jb_posint, jb_label, jb_doc, jb_dom;
DROP TYPE jb_lvl3;
DROP EXTENSION pg_sift;