You can remove given keys with the -
operator (the inverse of what you have in mind). The manual:
jsonb - text[]
→ jsonb
Deletes all matching keys or array elements from the left operand.
'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
→ {}
But I wouldn't know of a built-in function or operator doing what you ask (incl. the current Postgres 15).
Remarkably, it's the other way round with SQL SELECT
statements. There you get a positive list with SELECT a,d,e FROM tbl
, but cannot simply get "all columns except [a,d,e]" like you can get "all keys except [a,d,e]" from a JSON object with the above operator. I would love to have the complementary feature for each.
Also can't think of an easy way with the SQL/JSON path language (Postgres 12+).
Workaround for jsonb
You can create a simple function like:
CREATE OR REPLACE FUNCTION f_jsonb_select_keys(_js jsonb, _keys text[])
RETURNS jsonb
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT jsonb_object_agg(t.key, t.value)
FROM jsonb_each(_js) t
WHERE t.key = ANY (_keys);
$func$;
db<>fiddle here
Call:
SELECT f_jsonb_select_keys('{"a":42,"b":43,"c":44,"d":97}', '{a,d,e}');
Returns:
{"a":42,"d":97}
You get NULL for NULL or empty input for either argument (as demonstrated in the fiddle). So I declared the function STRICT
(a.k.a. RETURNS NULL ON NULL INPUT
), even though the nested function jsonb_object_agg()
is not STRICT
. That could mess with function inlining, but since this one cannot be inlined anyway (containing an aggregate function), we might as well. See:
IMMUTABLE
is only kind of true. Keep reading.
Order of keys?
jsonb
does not preserve the order of keys (keys are ordered in deterministic fashion internally), so we need not bother about the order of input rows in the aggregation. Except when there can be duplicates, then the latest copy from the input prevails. The manual:
Because the json
type stores an exact copy of the input text, it
will preserve semantically-insignificant white space between tokens,
as well as the order of keys within JSON objects. Also, if a JSON
object within the value contains the same key more than once, all the
key/value pairs are kept. (The processing functions consider the last
value as the operative one.) By contrast, jsonb
does not preserve
white space, does not preserve the order of object keys, and does not
keep duplicate object keys. If duplicate keys are specified in the
input, only the last value is kept.
Meaning, if there can be duplicate keys in the input, the order of rows is significant. My function does not explicitly order rows, so the result would not strictly be IMMUTABLE
. But the input comes directly from jsonb_each()
and there cannot be duplicate keys in jsonb
(unlike json
). Possible duplicates in the input array of keys are irrelevant for the = ANY
construct. So duplicates can never happen. IMMUTABLE
after all.
Again, this would prohibit function inlining - if it could be inlined to begin with.
Equivalent for json
For completeness: the same for json
instead of jsonb
:
The first variant does not try to preserve the given order of keys, and it eliminates possible duplicates in the input array of keys (but not possible duplicates from the json
input!):
CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
RETURNS json
LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value)
FROM json_each(_js) t
WHERE key = ANY (_keys);
$func$;
The second variant preserves the given order of keys and keeps all possible duplicates. (If the same key is 2x in the json
input, and 3x in the input array of keys, you get it 6x in the result.):
CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[])
RETURNS json
LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT json_object_agg (t.key, t.value ORDER BY ord)
FROM unnest(_keys) WITH ORDINALITY k(key, ord)
JOIN json_each(_js) t USING (key);
$func$;
About WITH ORDINALITY
:
I made the json
functions STABLE
because json_object_agg()
is only STABLE
as opposed to jsonb_object_agg()
, which is IMMUTABLE
.
You could use json_build_object ('a', js->'a', 'd', js->'d', 'e', js->'e')
but that would include all keys, with a NULL value if not found. Not exactly your request. And you couldn't tell the difference between a missing key and the same key with an actual NULL value.
jsonb
already enforces unique keys (whilejson
does not). I elaborated and added a quote from the manual below.