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

Question

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"

Related: How to convert Postgres json(b) to integer?, to float?, to text? — Bergi
– Bergi, Commented Jul 7, 2022 at 0:27

klin · Accepted Answer · 2018-10-19 09:44:26Z

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

Chris Magnuson · Accepted Answer · 2024-08-09 15:06:34Z

3

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

edited Aug 9, 2024 at 15:06

answered Jan 24, 2024 at 21:39

Chris Magnuson

5,9997 gold badges37 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related