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:

    {
    "dynamicFields":[
    {
    "name":"200",
    "hidden":false,
    "subfields":[
    {
    "name":"a",
    "value":"Subfield a"
    },
    {
    "name":"b",
    "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