CODEWITHSUNDEEP
jsonpostgresqljsonb
dipole_moment
dipole_moment

Reputation: 5854

In postgresql, how can I return a boolean value instead of string on a jsonb key?

In the query below, $isComplete and $isValid are returned as a string. However, they are saved as boolean values. How can I get the boolean representation of these fields to be returned?

query =
    "SELECT
        data #>> '{id}' AS id,
        data #>> '{name}' AS name,
        data #>> '{curator}' AS curator,
        data #>  '{$isValid}' as \"$isValid\",
        data #>  '{customer}' as customer,
        data #>  '{$createdTS}' as \"$createdTS\",
        data #>  '{$updatedTS}' as \"$updatedTS\",
        data #>  '{$isComplete}' as \"$isComplete\",
        (count(keys))::numeric as \"numProducts\"
    FROM
      appointment_intakes,
      LATERAL jsonb_object_keys(data #> '{products}') keys
    GROUP BY id"

Upvotes: 52

Views: 45324

Answers (2)

Chris Magnuson
Chris Magnuson

Reputation: 5949

In Postgres 14 and higher you can use JSONB Subscripting to drop the ->> and the ():

select id, data, data['is_boolean']::boolean as is_boolean
from jsonb_test
where data['is_boolean']::boolean

Upvotes: 2

klin
klin

Reputation: 121634

Simply cast a text to boolean:

create table jsonb_test (id int, data jsonb);
insert into jsonb_test values
(1, '{"is_boolean" : true}'),
(2, '{"is_boolean" : false}');

select id, data, (data->>'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->>'is_boolean')::boolean

 id |          data          | is_boolean 
----+------------------------+------------
  1 | {"is_boolean": true}   | t
(1 row)

Note that you can also cast other json text values to boolean, examples:

insert into jsonb_test values
(3, '{"is_boolean" : "true"}'),
(4, '{"is_boolean" : "false"}'),
(5, '{"is_boolean" : "t"}'),
(6, '{"is_boolean" : "f"}'),
(7, '{"is_boolean" : "on"}'),
(8, '{"is_boolean" : "off"}');

select id, data, (data->>'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->>'is_boolean')::boolean

 id |          data          | is_boolean 
----+------------------------+------------
  1 | {"is_boolean": true}   | t
  3 | {"is_boolean": "true"} | t
  5 | {"is_boolean": "t"}    | t
  7 | {"is_boolean": "on"}   | t
(4 rows)

Read about valid literals for boolean type in the documentation.

Update

Postgres 11 adds casts from JSONB scalars to numeric and boolean data types. This query will work only for regular boolean JSONB scalars (i.e. true or false):

select id, data, (data->'is_boolean')::boolean as is_boolean
from jsonb_test
where (data->'is_boolean')::boolean

Upvotes: 77

Related Questions