Get all keys of jsonb object in postgresql table where value is true

Question

I have a postgresql table customers with name and features columns.

features contains jsonb objects like {"featureA": true, "featureB": false, "featureC":true}

What I'd like to get is an array of those keys in features where the value is true for each name, like:

name      | features
----------|---------------------
customerA | [featureA, featureC]
customerB | [featureB, featureC]

From this post, I learnt that

SELECT key
FROM jsonb_each()
WHERE value = jsonb 'true'

is how you get keys that are true, but how do I do that for my table customers?

Something like

SELECT array_agg(key)
FROM   jsonb_each((select features from customers))
WHERE  value = jsonb 'true'

returns SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression.

Any help would be appreciated.

Answer 1

You are describing a lateral join:

select c.name, x.keys
from customers c
cross join lateral (
    select array_agg(x.key) keys
    from jsonb_each(c.features) x
    where x.value = jsonb 'true'
) x

Answer 2

It is also possible to use jsonb_each() in the select clause like this:

select
    x.name,
    json_agg(x.feature_name) as features
from (
    select
        c.name,
        row_to_json(jsonb_each(c.features))->>'key' as feature_name,
        (row_to_json(jsonb_each(c.features))->>'value')::bool as value
    from customers c
) as x
where x.value
group by x.name;