CODEWITHSUNDEEP
postgresqljsonb
Juraj
Juraj

Reputation: 6598

Extracting key names with true values from JSONB object

I'm trying to select keys from JSONB type with true values. So far I managed to do that using this query but I feel like there is a better way:

SELECT json.key
    FROM jsonb_each_text('{"aaa": true, "bbb": false}'::JSONB) json 
    WHERE json.value = 'true';

What I don't like is the WHERE clause where I'm comparing strings. Is there a way to cast it to boolean?
If yes, would it work for truthy and falsy values too? (explanation of truthy and falsy values in javascript: http://www.codeproject.com/Articles/713894/Truthy-Vs-Falsy-Values-in-JavaScript).

Upvotes: 0

Views: 744

Answers (1)

pozs
pozs

Reputation: 36234

jsonb has an equality operator (=; unlike json), so you could write

SELECT key
FROM   jsonb_each('{"aaa": true, "bbb": false}')
WHERE  value = jsonb 'true'

(with jsonb_each_text() you rely on some JSON values' text representation).

You can even include some additional values, if you want:

WHERE  value IN (to_jsonb(TRUE), jsonb '"true"', to_jsonb('truthy'))

IN uses the equality operator under the hood.

Upvotes: 2

Related Questions