You want to test a given key for a range of numeric values (not just for equality), but existing jsonb
operators do not provide such functionality, even less with index support.
Makes the task difficult to speed up, but there are still options. The best solution depends on data distribution, value frequencies, typical queries, avg. column size and other undisclosed details.
Assuming the general case that you want to test for arbitrary names and values and return the whole row.
SELECT *
FROM tbl t
WHERE EXISTS (
SELECT FROM jsonb_array_elements(t.jdata->'array') j
WHERE j->>'name' = 'test1' -- !
AND (j->>'value')::numeric > 4.5
)
AND t.jdata->'array' @> '[{"name": "test1"}]';
The outer predicate AND t.jdata->'array' @> '[{"name": "test1"}]'
is logically redundant and only makes sense if you expect a small percentage of rows to even have 'test1', in which case the query can make good use of this jsonb_path_ops
index:
CREATE INDEX tbl_jdata_arr_test1_idx ON tbl USING gin ((jdata->'array') jsonb_path_ops);
Don't index the whole jsonb
column for this purpose, would just make the index bigger and slower. Only put the nested array into the index and make sure the WHERE
condition of the query matches.
If you're only ever interested in 'test1', make it a partial index instead:
CREATE INDEX ...
WHERE jdata->'array' @> '[{"name": "test1"}]';
We must repeat the test j->>'name' = 'test1'
in the EXISTS
expression to assert the object with "name":"test1"
provides the value > 4.5 - and not just any object in the array.
Also, I would rather cast to numeric
, not float
. The implicit rounding of floating point numbers might make your test fail in extreme cases.
Related:
More specialized, smaller and faster indexes are possible, for instance by extracting a Postgres array of names and only indexing that. Related:
CREATE TABLE
statements for the tables in question adding the desired output. Use formatted text please, no screen shots