Search element from array of array in Postgres JSONB

Question

I have a table dealers in PostgreSQL. It looks like this:

insert into dealers values
  ('{"name":"Henry", "branches":[{"importeurs":[{"akz":"SON",number:"123456"}, {"akz":"ISA",number:"456789"}]}]}'),
  ('{"name":"Mike", "branches":[{"importeurs":[{"akz":"KIN",number:"133232"}, {"akz":"BAB",number:"767676"}]}]}'),
  ('{"name":"Sam", "branches":[{"importeurs":[{"akz":"DOM",number:"125454"}, {"akz":"QEE",number:"565665"}]}]}'),;

name column is string and branches column JSONB.

My question is: How to write a select query with given BAB and 767676 to get record Mike?

I tried this

SELECT * FROM dealers WHERE branches ->'importeurs' @> '[{"akz": "BAB", "number": "767676"}]';

I expected recode Mike back, but it returns nothing.

--------------updated--------------------

In Pic:

query returns result
query has error ERROR: operator does not exist: jsonb @? unknown

Please do not post code as images. See here for more details why: meta.stackoverflow.com/questions/285551 — user330315
– user330315, Commented Nov 18, 2022 at 10:54

score 1 · Accepted Answer · 2022-11-18 10:47:03Z

1

branches and importeurs are both JSON arrays, so you need to pass an array to the @> operator:

select *
from dealers
where branches -> 'branches' @> '[{"importeurs": [{"akz": "BAB", "number": "767676"}]}]';

Another option is to use a JSON path query:

where branches @? '$.branches[*].importeurs[*] ? (@.akz == "BAB" && @."number" == "767676")'

Online example

edited Nov 18, 2022 at 10:47

answered Nov 18, 2022 at 9:51

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Zegarek Over a year ago

demo

J.Z. Over a year ago

@a_horse_with_no_name thank for your response. I tried your queries, first one return nothing, second one has error, please see my update pic

user330315 Over a year ago

@J.Z.: both work for me

user330315 Over a year ago

@J.Z.: if the second throws an error, you are probably using an older Postgres version.

user330315 Over a year ago

@J.Z.: yes, Postgres 11 did not have JSON path support (that was introduced in 12)

Collectives™ on Stack Overflow

Search element from array of array in Postgres JSONB

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related