CODEWITHSUNDEEP
postgresqljsonb
Akshay Rawat
Akshay Rawat

Reputation: 4784

Query rows for matching JSONB column where key ends with a name and key value is a specific value

Given the following rows with a jsonb column details. How do I write a query so that records where the key name ends with _col with value B are selected. So records with ids 1, 2. id | details 1 | { "one_col": "A", "two_col": "B" } 2 | { "three_col": "B" } 3 | { another: "B" }

So far I've only find ways to match based on the value, not the key.

Upvotes: 0

Views: 276

Answers (1)

klin
klin

Reputation: 121594

Use the function jsonb_each_text() which gives json objects as pairs (key, value):

with the_data(id, details) as (
values
(1, '{ "one_col": "A", "two_col": "B" }'::jsonb),
(2, '{ "three_col": "B" }'),
(3, '{ "another": "B" }')
)

select t.*
from the_data t,
lateral jsonb_each_text(details)
where key like '%_col'
and value = 'B';

 id |             details              
----+----------------------------------
  1 | {"one_col": "A", "two_col": "B"}
  2 | {"three_col": "B"}
(2 rows)

Upvotes: 1

Related Questions