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
