clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335470 fiddles created (27410 in the last week).

CREATE TABLE tbl (id int, js jsonb); INSERT INTO tbl VALUES (1, '{"a":42,"b":43,"c":44,"d":97}') , (2, '{"a":42,"b":43,"c":44,"d":97,"e":{"a":[1,2,3]}}') , (3, '{"a":42}') , (4, '{}') , (5, NULL) ; 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 key = ANY (_keys); $func$;
5 rows affected
 hidden batch(es)


-- jsonb orders key names regardless of input order SELECT * , f_jsonb_select_keys(js, '{a,e,d}') AS ade -- note order of keys , f_jsonb_select_keys(js, '{}') AS empty_input , f_jsonb_select_keys(js, NULL) AS null_input FROM tbl;
id js ade empty_input null_input
1 {"a": 42, "b": 43, "c": 44, "d": 97} {"a": 42, "d": 97}
2 {"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}} {"a": 42, "d": 97, "e": {"a": [1, 2, 3]}}
3 {"a": 42} {"a": 42}
4 {}
5
 hidden batch(es)


-- Equivalent json instead of jsonb (WITHOUT preserving order of keys): CREATE OR REPLACE FUNCTION f_json_select_keys(_js json, _keys text[]) RETURNS json LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS $func$ SELECT json_object_agg (t.key, t.value) FROM json_each(_js) t WHERE key = ANY (_keys); $func$;
 hidden batch(es)


SELECT * , f_json_select_keys(js::json, '{a,e,d}') AS ade -- note changed order of keys , f_json_select_keys(js::json, '{}') AS empty_input , f_json_select_keys(js::json, NULL) AS null_input FROM tbl;
id js ade empty_input null_input
1 {"a": 42, "b": 43, "c": 44, "d": 97} { "a" : 42, "d" : 97 }
2 {"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}} { "a" : 42, "d" : 97, "e" : {"a": [1, 2, 3]} }
3 {"a": 42} { "a" : 42 }
4 {}
5
 hidden batch(es)


-- Equivalent json instead of jsonb (WITH preserving order of keys): 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$;
 hidden batch(es)


SELECT * , f_json_select_keys(js::json, '{a,e,d}') AS ade -- note changed order of keys , f_json_select_keys(js::json, '{}') AS empty_input , f_json_select_keys(js::json, NULL) AS null_input FROM tbl;
id js ade empty_input null_input
1 {"a": 42, "b": 43, "c": 44, "d": 97} { "a" : 42, "d" : 97 }
2 {"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}} { "a" : 42, "e" : {"a": [1, 2, 3]}, "d" : 97 }
3 {"a": 42} { "a" : 42 }
4 {}
5
 hidden batch(es)