Thread: Working with JSONB data having node lists

JSONB fields are very attractive for our current use, particularly as straight key-value pairs in the JSONB data;

but we are having trouble finding documentation on how to query lists (of scalars or objects) in nodes of the JSONB
data.

~~~

I have the table as follows:

CREATE TABLE public.contacts
(
    id integer NOT NULL DEFAULT nextval('contacts_id_seq'::regclass),
    uuid uuid NOT NULL DEFAULT gen_random_uuid(),
    vertical_id integer NOT NULL,
    inboundlog_id integer NOT NULL,
    email character varying(255) COLLATE pg_catalog."default" NOT NULL,
    data jsonb NOT NULL,
    created_at timestamp(0) without time zone,
    updated_at timestamp(0) without time zone,
    CONSTRAINT contacts_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

~~~

I have a record as follows:

INSERT INTO contacts
(uuid, vertical_id, inboundlog_id, email, data)
VALUES
(gen_random_uuid(), 1, 1, ‘phil@site.com',
 '{"first”:"Phil","last”:"Peters”,"subscriptions”:[101,202,303]}')


How do I craft a query to find all subscribers to program 202?

~~~

I have another record as follows:

INSERT INTO contacts
(uuid, vertical_id, inboundlog_id, email, data)
VALUES
(gen_random_uuid(), 1, 1, ‘bob@domain.com',
 '{"first”:"Bob","last”:"Baker”,"downloads":[{"date":"2018-01-01 00:00:00","pubid”:123},{"date":"2018-02-02
00:00:00","pubid”:456}]}')

How do I craft a query to find all contacts who have downloaded pubid 123?

TIA -
Geoff