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

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

Continue reading...