1

This is my query and it works. I store the list of dictionaries inside my jsonb column.

SELECT
    items.title
FROM
    items
WHERE
    jsonb_path_exists(items.types::jsonb, '$[*] ? (@.target == "discount")')

Is there any way to write this without jsonb_path_exists() function?

Also, do JSON Processing Functions use indexing?

I want to simplify the readability/look of my query because it is so long. And curious if I can get any performance improvements by not using JSON Processing Functions.

I tried to replace it with @? but failed. This is what I used (quote from PostgreSQL):

jsonb @? jsonpath → boolean

Does JSON path return any item for the specified JSON value?

'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' → t

Any help is very much appreciated.

Ulvi
  • 965
  • 12
  • 31
  • "*Do JSON Processing Functions use indexing?*" - the page you quoted also writes that "[Section 8.14.4](https://www.postgresql.org/docs/13/datatype-json.html#JSON-INDEXING) describes how these operators can be used to effectively search indexed jsonb data". – Bergi Aug 17 '21 at 01:19
  • "*I tried to replace it with `@?` but failed*" - please also show us the query you tried. How did it fail? – Bergi Aug 17 '21 at 01:23
  • 1
    Why are you casting the column to `jsonb` to begin with? If it's defined as `jsonb` the cast is useless (and you can shorten your query by removing it). If it's `json` (or even worse `text`) then you should convert it to `jsonb` so that you don't need the cast everywhere and indexes are easier to define (and use) –  Aug 17 '21 at 05:15

1 Answers1

2

The evaluation of the JSON path is a bit different between the jsonb_path_xxx() functions and the equivalent operator. Most importantly you do not need the ? (...) condition as that is implied when using the operator.

The following should be equivalent:

where items.types::jsonb @? '$[*].target == "discount"'

If you want to simplify or shorten your query, introduce an alias for items so that you don't need to repeat the complete table name everywhere. Converting the column types to jsonb would further simplify your query as you don't need the cast anymore.

And curious if I can get any performance improvements by not using JSON Processing Functions.

The @? operator can make use of a GIN index - but this only works for jsonb not for the json data type.

The above where condition could make use of the following index:

create index on items using gin ( (types::jsonb) );

The index definition could be simplified by removing the cast, if the column was correctly defined as jsonb.