Postgres 9.4 select by json array`s element value

Question

There is an 'accounts' table with 'data'::jsonb field filled with:

{
    "cars": [{"body": "E-JZA80-ALFQZ", 
         "year": 1999, 
         "brand": "Toyota", 
         "model": "Vista Ardeo"} 
     ], 
    "name": "Gilbert Moore", 
    "phone": "+13222314555"
}

I trying something like: select * from accounts where data->'cars' @> '{"brand":"Toyota"}' But it doesn`t show the record. What a have missed?

Answer 1

Your query expects the json value in the form:

{
    "cars": {"body": "E-JZA80-ALFQZ", 
         "year": 1999, 
         "brand": "Toyota", 
         "model": "Vista Ardeo"} 
     , 
    "name": "Gilbert Moore", 
    "phone": "+13222314555"
}

But in the actual data data->'cars' is an array, not an object, so the query should be:

select a.*
from accounts a
where data->'cars' @> '[{"brand":"Toyota"}]'

as operator @> applies to two objects or two arrays.