0

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.

2
  • What do you want the return to be? the full jdata if value > 9.5, or just the name and value? Mar 30, 2018 at 19:19
  • Please Edit your question and add the CREATE TABLE statements for the tables in question adding the desired output. Use formatted text please, no screen shots Mar 30, 2018 at 19:22

2 Answers 2

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:

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.