Working with JSONB data having node lists

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

Responses

Browse pgsql-general by date

  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