current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 1 down vote favorite

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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. – Viktor Sidochenko Aug 2 at 17:26
1  
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. – klin Aug 2 at 20:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.