Working with Postgres 14, I need to select all rows from a table that have at least one value in the "categories" field. The type of "categories" field is JSONB.
My "categories" field in the db contains an array of numbers:
[20,23,24]
I'm trying this:
jsonb_exists_any( finishes.categories, ARRAY[20, 23] )
But I get this error:
ERROR: cannot cast type integer[] to json LINE 5: AND jsonb_exists_any( categories, ARRAY[20, 23]::json )
It works fine if I replace the data in the field with an array of strings. Ex:
["20","23","24"]
and then:
jsonb_exists_any( finishes.categories, ARRAY['20', '23'] )
But that's not what I want to achieve.
Any hints?
jsonb_exists_any() is implemented in Postgres since version 9.4. But it's not meant as user-facing function, just to implement the jsonb operator ?|, which you would rather use:
SELECT jsonb '[20,23,24]' ?| '{20, 23}'::text[]; -- false (!)
Still doesn't work the way you want it because the operator (like the function) answers the question, quoting the manual:
Do any of the strings in the text array exist as top-level keys or array elements?
Note the term "strings". Elements of your JSON array are numbers not strings. Consider:
SELECT jsonb_typeof (jsonb '20') -- number
, jsonb_typeof (jsonb '"20"'); -- string
That's why it works when you convert the JSON array elements to strings:
SELECT jsonb '["20", "23", "24"]' ?| '{20, 23}'::text[]; -- true
Even this works:
SELECT jsonb '[20, "23", 24, {"foo":"bar"}]' ?| '{20, 23}'; -- true
Unlike Postgres arrays, JSON arrays allow heterogeneous element types.
And the right operand is coerced to text[] automatically while operator resolution isn't ambiguous.
Related:
To my knowledge there is still (Postgres 17 at the time of writing) no completely simple way to match any value of a given array.
Solutions include:
Combine the jsonb "contains" operator @> with an ANY construct.
@> expects a jsonb value to the right, so you can't pass a plain array (like int[] or numeric[]):
SELECT jsonb '[20,23,24]' @> ANY (ARRAY [jsonb '[22]', jsonb '[23]']);
Or:
SELECT jsonb '[20,23,24]' @> ANY ((ARRAY ['[22]', '[23]'])::jsonb[]);
Or with minimal syntax:
SELECT jsonb '[20,23,24]' @> ANY ('{[22],[23]}');
(The right operand is coerced automatically while operator resolution is unambiguous and the input is untyped.)
Since Postgres 12, you can also use the jsonpath operator @?:
SELECT jsonb '[20,23,24]' @? '$[*] ? (@ == 22 || @ == 23)';
Again, no suitable "overlaps" operator to check against a whole array or list of values, so you have to spell it out.
Postgres arrays offer the desired functionality. While it's all numbers, you can convert and use the array overlaps operator &&:
SELECT ARRAY(SELECT jsonb_array_elements_text(jsonb '[20,23,24]')::int) && '{22,23}'::int[];
... which opens up a range of options. See:
Related:
If the array is all there is, just use a plain Postgres array to begin with, no JSON wrapper, and you are golden:
SELECT '{20,23,24}'::int[] && '{22,23}'::int[];