Postgresql – Indexed range comparison of array key’s value in row inside JSONB

indexjsonpostgresql

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.

Best Answer

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 makes 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.

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

Related Solutions

PostgreSQL – How to Search Subkey Inside JSONB

I'm using this sql to get 'application' (field) -> 'tour_id.id'

Assuming you meant to ask:

I'm using this SQL query to retrieve the row where application #>> '{0, tour_id, id}' equals the provided UUID.

.. which already carries half an answer.

If your outer JSON array only has a single element like your example value implies, use this simple query:

SELECT *
FROM   payment
WHERE  application #>> '{0, tour_id, id}' = 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'
ORDER  BY "createdAt" DESC;

Note how I replaced the (assumingly) unneeded fuzziness of
like '%a43d38d4-9bfb-4ffa-8704-e536f04b0c60%'
with simpler and faster
= 'a43d38d4-9bfb-4ffa-8704-e536f04b0c60'.

Else (for any number of elements in the outer JSON array):

SELECT *
FROM   payment
WHERE  application @> '[{"tour_id": {"id":"a43d38d4-9bfb-4ffa-8704-e536f04b0c60"}}]'
ORDER  BY "createdAt" DESC;

That's the jsonb containment operator @>, which can be supported with a generic index or a more specialized jsonb_path_ops index. Instructions and links:

How to get particular object from jsonb array in PostgreSQL?

The manual hints:

For a full description of jsonb containment and existence semantics, see Section 8.14.3.

Indexing Subkey Inside Subkey in JSONB in PostgreSQL

First off, I would consider a normalized DB design instead of the convoluted jsonb column, which makes indexing and complex queries harder.

How to index by [array].customer_id.id?

See my answer to your previous question:

Index by subkey in jsonb

You could have two GIN indexes like instructed there, one on tour_id, another one on customer_id, or a single multicolumn index, and then query:

SELECT * FROM tbl
WHERE  f_extract_tour_ids(registration)     @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[]
AND    f_extract_customer_ids(registration) @> '{f492b26d-d260-4bcb-8e96-6532fdf38665}'::uuid[];

Note, this returns rows with tour_id and customer_id matching in any top-level object of the array, not necessarily in the same. If you want to find rows where tour_id and customer_id match in the same object, you have to do more ...

`jsonb_path_ops` index

However, with a growing number of different queries (requiring multiple special indexes), a single generic (much bigger) jsonb_path_ops index on the whole column becomes more competitive. Or even one with the default operator class operator class jsonb_ops - which is bigger, yet.

CREATE INDEX foo ON tbl USING GIN (registration jsonb_path_ops);

Queries to go with it:

For rows with tour_id and customer_id matching in the same top-level object:

SELECT * FROM tbl
WHERE  registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}
                        , "customer_id": {"id": "f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';

For rows with tour_id and customer_id matching in any top-level object, not necessarily the same:

SELECT * FROM tbl
WHERE  registration @> '[{"tour_id": {"id": "d61802ff-3eec-4a72-97ca-832f51b96bf0"}}
                       , {"customer_id":{"id":"f492b26d-d260-4bcb-8e96-6532fdf38665"}}]';

This solution is slower because of the much bigger index carrying all the other noise in your jsonb column. But it is much more versatile and simpler.
If the first solutions make your head ache, use this one.

Best Answer

Related Solutions

PostgreSQL – How to Search Subkey Inside JSONB

Indexing Subkey Inside Subkey in JSONB in PostgreSQL

jsonb_path_ops index

Related Question

`jsonb_path_ops` index