Search element from array of array in Postgres JSONB-CodePudding

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

CodePudding user response：

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