Select from table where jsonb array has elements with specific properties

Question

How can I select the rows in a table having a jsonb array that has elements with a specific property ?

Eg: If the jsonb is the following

{ "list" : [
     {"name": "John", "money": 100},  
     {"name": "Dan", "money": 900}
  ]
}

How can I select the rows having at least one element with name 'Dan' in the array ? I tried with:

select jsonb_pretty(data) from table where data -> 'list' @>  '{"name": "Dan"}';

but id does not return any rows.

what is your excepted value? the whole row or just {"name": "Dan", "money": 900} ? — FeanDoe
– FeanDoe, Commented Jan 5, 2018 at 15:22
SELECT jsonb_pretty(data) FROM table WHERE data -> 'list' @> '[{"name": "Dan"}]' ? — bma
– bma, Commented Jan 5, 2018 at 15:27
@FeanDoe Doesn't matter, I just want to be able to identify that row. — panagdu
– panagdu, Commented Jan 5, 2018 at 16:20
@bma Seems like this one works! Would you like to write an answer so that I can accept it? — panagdu
– panagdu, Commented Jan 5, 2018 at 16:22

bma · Accepted Answer · 2018-01-05 16:26:54Z

1

The key here is that the JSONB value (in the column) needs to match the filter:

SELECT jsonb_pretty(data)
FROM table
WHERE data -> 'list' @> '[{"name": "Dan"}]'

Note the [] around [{"name": "Dan"}] filter.

PostgreSQL JSON/JSONB Functions and Operators documentation for further reference.

answered Jan 5, 2018 at 16:26

bma

9,8562 gold badges35 silver badges22 bronze badges

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

1 Answer 1