Re: Working with JSONB data having node lists
| От | David G. Johnston |
|---|---|
| Тема | Re: Working with JSONB data having node lists |
| Дата | |
| Msg-id | CAKFQuwYgiS9ujBevW-Fes-tB-pZMwibLa7Yc+HyTby_OaKJf9g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Working with JSONB data having node lists (geoff hoffman <geoff@rxmg.com>) |
| Ответы |
RE: Working with JSONB data having node 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.
'{"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 по дате отправления: