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?

klin · Accepted Answer · 2015-08-03 00:17:58Z

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.

edited Aug 3, 2015 at 0:17

answered Aug 1, 2015 at 21:56

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Viktor Sidochenko Over a year ago

I like the cross join solution. But what about operation cost? For example, I have 10 millions of records with 1-3 cars in each.

klin Over a year ago

I think the query against 10 million rows may take one or few minutes. With large amount of data I personally would prefer to convert a json to standardized tables. Initially you have to spend extra time to design tables and insert/update functions, but in the long run it brings tangible benefits. In principle I do not use jsonb type if the number of rows may be greater than some 100 000.

Collectives™ on Stack Overflow

Postgres 9.4 select by json array`s element value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related