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

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[] = '{}'

CodePudding user response：

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

CodePudding user response：

You can use jsonb_object_keys function which returns the set of keys in the top-level JSON object.
See teh full description here https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

Using this function your task could be solved such way

WITH key AS (
  SELECT jsonb_object_keys(t.source) AS value
  FROM (SELECT '{"firstname": "John", "secondname": "Smith", "age": 55}'::jsonb AS source) t
) SELECT * FROM key
WHERE key.value IN ('firstname', 'secondname')