select子句，其中id为json数组

Question

我有一个字符串形式的uuidsjson数组，我想在选择查询中使用它:

SELECT "Animals" WHERE "id" = jsonb_array_elements_text(
'["2ffae132-7a46-11eb-8be3-a483e71a4d0b",
"2ffae132-7a46-11eb-8be3-a483e71a4d0c",
"2ffae132-7a46-11eb-8be3-a483e71a4d0d"]');

给出这个错误

ERROR:  set-returning functions are not allowed in WHERE
LINE 1: ...* from "Animals" WHERE "id" = jsonb_arra...
^
SQL state: 0A000
Character: 58

Answer 1

为了使用集合返回函数，您需要执行sub-SELECT:

edb=# create table animals (id text, name text);
CREATE TABLE
edb=# insert into animals values ('2ffae132-7a46-11eb-8be3-a483e71a4d0b','foo');
INSERT 0 1
edb=# SELECT * from animals WHERE "id" in (select jsonb_array_elements_text(
'["2ffae132-7a46-11eb-8be3-a483e71a4d0b",
"2ffae132-7a46-11eb-8be3-a483e71a4d0c",
"2ffae132-7a46-11eb-8be3-a483e71a4d0d"]'));
id                  | name 
--------------------------------------+------
2ffae132-7a46-11eb-8be3-a483e71a4d0b | foo
(1 row)