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

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