Re: Working with JSONB data having node lists

On Tue, Jan 30, 2018 at 2:47 PM, geoff hoffman <geoff(at)rxmg(dot)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.
​