Home > mailing lists

Re: Working with JSONB data having node lists - Mailing list pgsql-general

From	David G. Johnston
Subject	Re: Working with JSONB data having node lists
Date	January 31, 2018 04:14:33
Msg-id	CAKFQuwYgiS9ujBevW-Fes-tB-pZMwibLa7Yc+HyTby_OaKJf9g@mail.gmail.com Whole thread Raw
In response to	Working with JSONB data having node lists (geoff hoffman <geoff@rxmg.com>)
Responses	RE: Working with JSONB data having node lists (Steven Winfield <Steven.Winfield@cantabcapital.com>)
List	pgsql-general

Tree view

On Tue, Jan 30, 2018 at 2:47 PM, geoff hoffman <geoff@rxmg.com> wrote:

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.

https://www.postgresql.org/docs/10/static/functions-json.html

'{"first”:"Phil","last”:"Peters”,"subscriptions”:[101,202,303]}')

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

SELECT data->'subscriptions' ? '202'

The docs speak of "top-level keys" but that also includes array element values.

'{"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?

If you can do this one without unnesting the downloads array I do not know how. Having done that: (WHERE dlarray->'publd' = '123)

David J.

pgsql-general by date:

From: Thomas Kellerer
Date: 31 January 2018, 04:03:18
Subject: Re: Alter view with dependence without drop view!

From: "Ivan E. Panchenko"
Date: 31 January 2018, 04:50:46
Subject: Re: How to Optimize pg_trgm Performance

Re: Working with JSONB data having node lists - Mailing list pgsql-general

Previous

Next