From: | geoff hoffman <geoff(at)rxmg(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Working with JSONB data having node lists |
Date: | 2018-01-30 21:47:21 |
Message-ID: | 93BF39AF-F96B-4437-BECE-CD7D7815CF4E@rxmg.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)site(dot)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(at)domain(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2018-01-30 21:50:58 | Re: Alter view with dependence without drop view! |
Previous Message | Jan Wieck | 2018-01-30 21:43:50 | Re: ERROR: invalid memory alloc request size 1073741824 |