1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 25, 2021.

  1. Stack

    Stack Membro Participativo

    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...

Compartilhe esta Página