jsonpostgresqljsonb

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"

Solution

  • 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