Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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$;

CREATE TABLE



INSERT 0 5



CREATE FUNCTION


-- 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}
null
null

2
{"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}}
{"a": 42, "d": 97, "e": {"a": [1, 2, 3]}}
null
null

3
{"a": 42}
{"a": 42}
null
null

4
{}
null
null
null

5
null
null
null
null



SELECT 5


-- 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$;

CREATE FUNCTION


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 }
null
null

2
{"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}}
{ "a" : 42, "d" : 97, "e" : {"a": [1, 2, 3]} }
null
null

3
{"a": 42}
{ "a" : 42 }
null
null

4
{}
null
null
null

5
null
null
null
null



SELECT 5


-- 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$;

CREATE FUNCTION


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 }
null
null

2
{"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}}
{ "a" : 42, "e" : {"a": [1, 2, 3]}, "d" : 97 }
null
null

3
{"a": 42}
{ "a" : 42 }
null
null

4
{}
null
null
null

5
null
null
null
null



SELECT 5

id	js	ade	empty_input	null_input
1	{"a": 42, "b": 43, "c": 44, "d": 97}	{"a": 42, "d": 97}	null	null
2	{"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}}	{"a": 42, "d": 97, "e": {"a": [1, 2, 3]}}	null	null
3	{"a": 42}	{"a": 42}	null	null
4	{}	null	null	null
5	null	null	null	null

id	js	ade	empty_input	null_input
1	{"a": 42, "b": 43, "c": 44, "d": 97}	{ "a" : 42, "d" : 97 }	null	null
2	{"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}}	{ "a" : 42, "d" : 97, "e" : {"a": [1, 2, 3]} }	null	null
3	{"a": 42}	{ "a" : 42 }	null	null
4	{}	null	null	null
5	null	null	null	null

id	js	ade	empty_input	null_input
1	{"a": 42, "b": 43, "c": 44, "d": 97}	{ "a" : 42, "d" : 97 }	null	null
2	{"a": 42, "b": 43, "c": 44, "d": 97, "e": {"a": [1, 2, 3]}}	{ "a" : 42, "e" : {"a": [1, 2, 3]}, "d" : 97 }	null	null
3	{"a": 42}	{ "a" : 42 }	null	null
4	{}	null	null	null
5	null	null	null	null