Re: jsonb and where clause?

On Tue, 28 Nov 2017 11:28:55 +0300
"Ivan E. Panchenko" <i.panchenko@postgrespro.ru> wrote:

> Hi Bjorn,
>
> 28.11.2017 11:18, Bjorn T Johansen пишет:
> > Hi.
> >
> > Just starting to look at how to use jsonb columns and I have a question. I have found out that I can use the
followingto search for a value 
> > inside the jsonb column:
> >
> > select * from orders where info ->> 'customer' = 'John Doe'    (where info is the jsonb column)
> >
> >
> > But what if the jsonb column contains an json array, how can I search then?
> >
> > info -> [ { "customer" : "John Doe" } ]
>
> If you know the index in array, you can search like
> info->0->>'customer'
> If you want to search in any array element, you need to use JSQUERY
> extension,
> see https://github.com/postgrespro/jsquery
>
> >
> >
> > btw, using PostgreSQL 9.6 but will be moving to 10 soon.
> >
> >
> > Regards,
> >
> > BTJ
> >
> Regards,
> Ivan
>

Thx... :)

btw, just managed to use the following sql:

select * from orders where info @> '{"recs": [{ "customer":"John Doe"}]}'

(changed my json string to info -> { "recs: [ { "customer" : "John Doe" } ] }

And this seems to work but is this the "wrong" way of doing it or?


BTJ