jsonb and where clause?

Started by Bjørn T Johansenover 8 years ago5 messagesgeneral

Bjørn T Johansen

btj@havleik.no

over 8 years ago

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

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

Regards,

BTJ

Ivan E. Panchenko

i.panchenko@postgrespro.ru

over 8 years ago

In reply to: Bjørn T Johansen (#1)

Re: jsonb and where clause?

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

Bjørn T Johansen

btj@havleik.no

over 8 years ago

In reply to: Ivan E. Panchenko (#2)

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 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?

BTJ

Ivan E. Panchenko

i.panchenko@postgrespro.ru

over 8 years ago

In reply to: Bjørn T Johansen (#3)

Re: jsonb and where clause?

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 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

Bjørn T Johansen

btj@havleik.no

over 8 years ago

In reply to: Ivan E. Panchenko (#4)

Re: jsonb and where clause?

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 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

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

BTJ