Search code examples
postgresqlpostgresql-13postgresql-json

How to write jsonb inside WHERE without JSON Processing Functions

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.

Solution

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