在另一个查询的选择中查询一个jsonb数组

Question

我有一个用户表，其中有一个列收藏夹，它是一个jsonb

favorites:
[
{
"id_doc:": 9,
"type": "post"
},
{
"id_doc": 10,
"type": "post"
}
]

我还有另一个表帖子，我想通过id进行查询，这个id必须在收藏夹用户的字段id_doc中

select * from posts where id in (select favorites -> id_doc from users )

这是模式

CREATE TABLE dev.users
(
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
firstname character varying COLLATE pg_catalog."default" NOT NULL,
lastname character varying COLLATE pg_catalog."default" NOT NULL,
email character varying COLLATE pg_catalog."default" NOT NULL,
password character varying COLLATE pg_catalog."default" NOT NULL,
favorites jsonb[],
CONSTRAINT users_pkey PRIMARY KEY (id),
CONSTRAINT email_key UNIQUE (email)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE dev.users
OWNER to postgres;

CREATE TABLE dev.posts
(
id integer NOT NULL DEFAULT nextval('dev.posts_id_seq'::regclass),
title character varying COLLATE pg_catalog."default" NOT NULL,
userid integer NOT NULL,
description character varying COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT posts_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE dev.posts
OWNER to postgres;

我该怎么做？谢谢

Answer 1

还有其他方法可以实现这一点，但为了清晰起见，我更喜欢使用CTE。如果你对它的作用有疑问，请在评论中告诉我。

with elements as (
select jsonb_array_elements(favorites) as favitem
from users
), fav_ids as (
select distinct (favitem->>'id_doc')::int as id_doc
from elements
)
select p.*
from posts p
join fav_ids f on f.id_doc = p.id
;

更新由于列被定义为jsonb[]而不是json，因此我们需要unnest()而不是jsonb_array_elements():

with elements as (
select unnest(favorites) as favitem
from users
), fav_ids as (
select distinct (favitem->>'id_doc')::int as id_doc
from elements
)
select p.*
from posts p
join fav_ids f on f.id_doc = p.id
;