In the base of CreditPrograms there is a column "Data" containing a jsonb object of the following type:

 { "RegionsActivity":[ 23 ] }

I want to search the RegionsActivity array:

 select * from "CreditPrograms" WHERE 23 = ANY("Data"->'RegionsActivity')

This syntax was taken from the documentation https://postgrespro.ru/docs/postgrespro/9.5/arrays

but postgre gives the error:

 [42601] ERROR: syntax error at or near "array"

    1 answer 1

    An array of some type in postgresql and an array in a JSON document are two different things.

    For json arrays of numbers in postgresql support is still quite weak and I do not see something very suitable for the task in the lists of operators or functions . It is possible to perform the task as minimally as possible through a subquery with jsonb_array_elements_text :

     select * from "CreditPrograms" WHERE exists( select from jsonb_array_elements_text("Data"->'RegionsActivity') el where el = '23' )

    If query performance is needed (instead of viewing the entire table), then you need to recycle the data schema. Or, if this is not possible, create an immutable storage that converts "Data"->'RegionsActivity' into an array of a numeric type, build a gin index and search in this storage.

    Perhaps you are more suited to a third-party jsquery extension.

    • Very sorry, thank you. - Vasya Milovidov