Is it possible to create an index on the results of something like the following? Value is another jsonb obj.
SELECT data_timestamp, key, value FROM hit_count CROSS JOIN jsonb_each(data);
Creating an index on result of query
NaN
0
Viewed 38
1 Answers
The answer is yes, you can index JSON data, for instance, you can read about it here: https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-effectively-store-index-json-data-in-postgresql/
You will need to study GIN indexes, see https://www.postgresql.org/docs/current/gin-intro.html
Alternatively, you could create a table (or view or materialized view) to store the result of your query with possibly also creating a trigger to update them whenever hit_count records are being added/removed/updated.
Related