CODEWITHSUNDEEP
sqlpostgresqlwhere-clausejsonb
Hommer Smith
Hommer Smith

Reputation: 27862

Return rows filtering by jsonb object in array

I have a table called houses and it has two columns id and a jsonb column called details. The details column has the following structure:

[{ kind: "flat", price: 100 }, { kind: "circle", price: 10 }]

I want to get all the houses which details column has at least one object where kind is flat.

This is what I have tried:

select *
FROM houses
WHERE "details"->>'kind' = 'flat'

Upvotes: 1

Views: 44

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

You may use jsonb_array_elements

select h.*  from houses h cross join lateral 
    jsonb_array_elements(details) as j
where j->>'kind' = 'flat'

Demo

Upvotes: 1

Related Questions