Select multiple keys using array from JSON field with ->> or #>>?

Question

For the following JSON field:

'{"firstname": "John", "secondname": "Smith", "age": 55}'

how can I select the keys from the array {"firstname", "secondname"}? This array could dynamically change over queries, and the field may or may not contain those keys.

I know I can use the ->> or #>> operators to filter for single keys, or key-value pairs, but these don't do what I need.

I want an operator with the form json->>text[] where text[] is an array of keys.

Ideally, this query would return any matching keys (in the boolean OR sense). Some example outputs:

field->'{"firstname", "secondname"}'::text[] = '{"firstname": "John", "secondname": "Smith"}'
field->'{"firstname", "job"}'::text[] = '{"firstname": "John"}'
field->'{"job"}'::text[] = '{}'

Answer 1

You'd have to write your own function for that:

CREATE FUNCTION jsonb_filter_keys(j jsonb, k text[]) RETURNS jsonb
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT coalesce(jsonb_object_agg(key, value), '{}'::jsonb)
FROM jsonb_each(j) AS e(key,value)
WHERE key = ANY (k)$$;

Answer 2

There is a jsonb operator to achieve the opposite:

jsonb - text[] → jsonb

Deletes all matching keys or array elements from the left operand.

With the right twist, we can make it work for us:

SELECT js - ARRAY (SELECT jsonb_object_keys(js) EXCEPT ALL VALUES ('firstname'), ('secondname'))

Extract top-level keys as set with jsonb_object_keys(), remove keys of interest with SQL EXCEPT or a similar technique to get the complement (the set difference). See:

• Select rows which are not present in other table

Remove this complementary set of keys (after transforming to an array) to get your result, exactly.

I'd expect this to be faster than decomposing all objects, and rebuilding qualifying JSON objects. Maybe not for small and flat JSON objects, but for large and/or deeply nested ones.

Function for repeated use:

CREATE OR REPLACE  FUNCTION f_jsonb_filter_keys(js jsonb, VARIADIC keys text[])
  RETURNS jsonb
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT js - ARRAY (SELECT jsonb_object_keys(js) EXCEPT ALL SELECT unnest(keys))';

Call:

SELECT f_jsonb_filter_keys(js, 'firstname', 'secondname')

Or:

SELECT f_jsonb_filter_keys(js, VARIADIC '{firstname, secondname}')

db<>fiddle here

In Postgres 9.6 or later add PARALLEL SAFE or it will prevent parallel execution for any query using it.

I use a VARIADIC input parameter so we can conveniently pass key names as list or as array. See:

• Pass multiple values in single parameter

Rant

It's simple to select a subset of columns from a table in SQL:

SELECT firstname, secondname FROM tbl;

But there is no simple equivalent in the JSON tools.

Oddly, it's the other way round with "get all columns except this one column". That's simple with JSON, but hard with a SELECT from a table. See:

• Select columns inside json_agg

I feel like each is an omission on the other side.