Обсуждение: jsonb and where clause?

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 following
tosearch 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" } ]


btw, using PostgreSQL 9.6 but will be moving to 10 soon.


Regards,

BTJ

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

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

28.11.2017 13:25, Bjorn T Johansen пишет:
> 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?
Yes, definitely it works, and is be supported by GIN index.

Nevertheless, I recommend you to have a look at JSQUERY which allows 
more complex queries, also with index support.
>
>
> BTJ
>
Regards,
Ivan

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

> 28.11.2017 13:25, Bjorn T Johansen пишет:
> > 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?
> Yes, definitely it works, and is be supported by GIN index.
>
> Nevertheless, I recommend you to have a look at JSQUERY which allows
> more complex queries, also with index support.
> >
> >
> > BTJ
> >
> Regards,
> Ivan
>

Ok, will have a look at JSQUERY also... :)

BTJ