Indexed range comparison of array key's value in row inside JSONB

Question

This is for tests with results. The order and if the test was done can be vary per row.

column jdata
{"name": "Somedata",
 "array": [ {"name":"test0","value":4.6}, 
            {"name":"test2", "value": 6.7},
            {"name":"test3", "value": 7.5},
            {"name":"test1","value":4.6}
           ],
"otherstuff": "stuff"
}

I want to get all occurrences of test1 where value is gt 4.5. test1 can be in any position in the array.

The best I can come up with is doing an indexed scan for @> {"name": "test1"} to get the IDs then on that to somehow construct a path getting (jdata->'array'->(gettest1indexsubqueryoffsorts)->'value')::float > 4.5

An index could maybe be constructed that summarizes all the tests into {"test1": 4.6, "test2": xx.xx} but again range checking cannot be done on that or the data structure can be changed. Pointers would be great.

Answer 1

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:

• Speed up range test for key values nested in jsonb array of objects

More specialized, smaller and faster indexes are possible, for instance by extracting a Postgres array of names and only indexing that. Related:

• Search for nested values in jsonb array with greater operator
• Find rows containing a key in a JSONB array of records (my answer to your previous related question)
• Index for finding an element in a JSON array
• Index for finding combinations of attributes in JSON array

Answer 2

I think what you want is something like this. (If you want the full jdata)

SELECT tbl.*
FROM tbl
WHERE EXISTS (
  SELECT 1
  FROM jsonb_to_recordset(jdata->array) AS t(n text, v float)
  WHERE v > 9.5
) AS t(is_95)
AND jdata @> '{"name": "test1"}';

Or something like this if you just want the passing elements.

SELECT *
FROM tbl
CROSS JOIN LATERAL (
  SELECT n,t
  FROM jsonb_to_recordset(jdata->array) AS t(n text, v float)
  WHERE v > 9.5
) AS t(n,t)
AND jdata @> '{"name": "test1"}';

But it's really hard to tell when you don't give us actual data, or desired output.