Hello mailing list!
We have a JSONB column in a table that has a key (string) => value (int) structure. We want to be able to create a btree index that accepts an arbitrary key to allowing sorting by key.
Table Example:
+----+------------------------------------------+
| ID | JSONB |
+----+------------------------------------------+
| 1 | {"key_1": 20, "key_2": 30, "key_52": -1} |
| 2 | {"key_1": 10} |
+----+------------------------------------------+
Here is the kind of query we want to run:
select id, (jsonb ->> 'key_1')::int as sort_key
from my_table
where (jsonb ? 'key_1' and (jsonb ->> 'key_1')::int > 0) and (jsonb ? 'key_2' and jsonb ->> (jsonb ->> 'key_2')::int > 50)
order by sort_key desc
limit 100;
We know that we can create indexes for each individual key (create index my_table_key_1_idx on my_table using btree((jsonb -> 'key_1')) or using a partial index including the ? operator) but the issue is that there are around 5000 potential keys, which means 5000 indexes.
We tried doing the relational thing, and splitting the JSONB table into it's own separate table, which is great because we can use a simple btree index, but unfortunately this forces us to use weird queries such as:
select id, max(value) filter (where key = 'key_1') as sort_key
from my_table_split
where (
(key = 'key_1' and value > 0) or
(key = 'key_2' and value > 50)
)
group by id having count(*) = 2
order by sort_key desc
limit 100;
Such a query takes a disappointing long time to aggregate. This also has the disadvantage that if we wanted to expand my_table we'd have to do an inner join further decreasing performance.
Does anyone have any ideas on how to approach this in a for performant way with the Postgres we have today?
Thank you,
Rory.
