Select keys with boolean value true in PostgreSQL json query

Question

In my PostgreSQL 11.6 table I have a json field app_settings with data on the following format:

{
   "my_app": {
      "features": {
         "very_good_feature": true,
         "awesome_feature": false,
         "even_better_feature": true
      }
   }
}

I want to create a query that selects a list of feature names where the feature has the value true. So in the example above, I would like the result of the query simply to be 2 rows like this:

very_good_feature
even_better_feature

I can successfully select ALL the keys with the following query:

select
    json_object_keys(app_settings ->'my_app' -> 'features') as k
from
    my_table;

How can I write the where clause to only list the ones with true as value?

Oto Shavadze · Accepted Answer · 2020-05-21 17:30:51Z

1

try with json_each_text:

select j.* from my_table
join lateral json_each_text(app_settings->'my_app'->'features') j(k, v) on true
where
j.v = 'true'

edited May 21, 2020 at 17:30

answered May 21, 2020 at 17:25

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

2 Comments

Mr. Developerdude Over a year ago

Great! That worked. I am curious, what kind of performance can I expect from this query? Would I be better off by rearranging my data in a way that allows for faster query?

Oto Shavadze Over a year ago

Nice to help! If in rearrange you mean, save data in general table/sql-ish way (instead of json), then of course operate with it, would be more performant

Collectives™ on Stack Overflow

Select keys with boolean value true in PostgreSQL json query

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