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

[SQL] Add and use index for jsonb with nested arrays

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

  1. Stack

    Stack Membro Participativo

    In my PostgreSQL 11.11 I have one jsonb column that holds objects like this:

    "value":"Subfield a"
    "value":"Subfield b"

    dynamicFields is an array and subfields is also an array and I having performance issues when hitting selects like this:

    select *
    from my_table a
    cross join lateral jsonb_array_elements(jsonb_column -> 'dynamicFields') df
    cross join lateral jsonb_array_elements(df -> 'subfields') sf
    where df ->> 'name' = '200' and sf ->> 'name' = 'a'

    The performance issues live mostly in the subfield. I have already added an index like this:

    CREATE INDEX idx_my_index ON my_table USING gin ((marc->'dynamicFields') jsonb_path_ops);

    How can I add an index for the subfields inside the dynamicFields?
    The query above is just one example, I use it a lot in joins with other tables in the database. And I also know the @> operator.

    Continue reading...

Compartilhe esta Página