sql - Find rows where a nested JSON array shares any elements with a given array - Stack Overflow

admin管理员组

文章数量:1026900

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?

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?

• sql
• arrays
• postgresql
• jsonb
• postgresql-14

Share Improve this question Follow edited Nov 17, 2024 at 6:17 Erwin Brandstetter 662k158158 gold badges1.2k1.2k silver badges1.3k1.3k bronze badges asked Nov 16, 2024 at 15:22 RobertoRoberto 80911 gold badge1414 silver badges3030 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Explanation

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:

• Query JSONB column for any value where =?

Solution

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:

• How to turn JSON array into Postgres array?

Related:

• Query JSON array of objects against multiple values

Better solution

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[];

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?

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?

• sql
• arrays
• postgresql
• jsonb
• postgresql-14

Share Improve this question Follow edited Nov 17, 2024 at 6:17 Erwin Brandstetter 662k158158 gold badges1.2k1.2k silver badges1.3k1.3k bronze badges asked Nov 16, 2024 at 15:22 RobertoRoberto 80911 gold badge1414 silver badges3030 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Explanation

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:

• Query JSONB column for any value where =?

Solution

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:

• How to turn JSON array into Postgres array?

Related:

• Query JSON array of objects against multiple values

Better solution

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[];

本文标签： sqlFind rows where a nested JSON array shares any elements with a given arrayStack Overflow