On Tue, Jan 30, 2018 at 2:47 PM, geoff hoffman <ge...@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.
