Menu
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" 
798
Querying the JSON document PostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
Jsonb stores the data as binary code. Basically, it stores the data in binary form which is not an ASCII/ UTF-8 string.
JSONB and Indexes PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by the GIN JSONB operator class.
The jsonb datatype is an advanced binary storage format with full processing, indexing and searching capabilities, and as such pre-processes the JSON data to an internal format, which does include a single value per key; and also isn't sensible to extra whitespace or indentation.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
