query a jsonb array in a select of another query

Question

I have a user table with a column favorites that is a jsonb

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

And I have another table posts where I want to make a query by id and this id must be in the fields id_doc in the favorites user

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

This is the schema

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;

How can I do this? Thank you

Mike Organek · Accepted Answer · 2020-07-07 17:12:20Z

2

There are other ways to accomplish this, but I prefer using CTEs for clarity. Please let me know in the comments if you have questions about what this does.

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
;

Update Since the column is defined as jsonb[] rather than json, we need to unnest() instead of 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
;

edited Jul 7, 2020 at 17:12

answered Jul 7, 2020 at 16:56

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

simo9900 Over a year ago

I have an error. jsonb_array_elements(jsonb[]) does not exist

Mike Organek Over a year ago

@simo9900 Please update your question to show the table schema. We have to treat jsonb[] columns differently than jsonb. I will update my answer.

Collectives™ on Stack Overflow

query a jsonb array in a select of another query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related