From: | "Ivan E(dot) Panchenko" <i(dot)panchenko(at)postgrespro(dot)ru> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: jsonb and where clause? |
Date: | 2017-11-28 10:52:59 |
Message-ID: | 7461ce91-bbbe-0389-9aac-3c68a0d5d240@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
28.11.2017 13:25, Bjorn T Johansen пишет:
> On Tue, 28 Nov 2017 11:28:55 +0300
> "Ivan E. Panchenko" <i(dot)panchenko(at)postgrespro(dot)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 following to 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bjorn T Johansen | 2017-11-28 11:07:58 | Re: jsonb and where clause? |
Previous Message | Bjorn T Johansen | 2017-11-28 10:25:41 | Re: jsonb and where clause? |