CODEWITHSUNDEEP
postgresqljsonb
Christopher Causer
Christopher Causer

Reputation: 1494

Postgresql format function of boolean values cast to jsonb

When I try to format() a postgresql boolean value and cast the value to a jsonb object, PostgreSQL raises an exception:

# select format('%s', true)::jsonb;
ERROR:  invalid input syntax for type json
DETAIL:  Token "t" is invalid.
CONTEXT:  JSON data, line 1: t

I am fairly sure that formatting a boolean value gives either t or f rather than the full word, which is what json expects it to be.

There already is a solution to this by using the case statement

 select format('%s', case when true then 'true' else 'false' end)::jsonb;
 format 
--------
  true
(1 row)

However, while this may seem fine for the trivial case above, when it's used in functions and anything more complicated it looks quite out of place. Is there a neater solution to this?

The context is that I have a function returning a hash of keys mapping to boolean flags, which are decided by functions. A sample of what one function could look like is

return format( '{
        "is_valid" : %s,
        "is_authorized" : %s,
        "is_not_delegated" : %s,
        }',
        is_valid(function_argument),
        is_authorized(function_argument),
        not is_delegated(function_argument)
 )::jsonb;

Upvotes: 1

Views: 1129

Answers (3)

Constantin Voita
Constantin Voita

Reputation: 1

You can use something like this:

select format('%s', true::text)::jsonb;

Upvotes: 0

Constantin Voita
Constantin Voita

Reputation: 1

You can use cast from boolean to text like as my_column::text

select format('{"level":%s,"cluster":%s}', rl.level, rl.cluster::text)

Upvotes: 0

cetver
cetver

Reputation: 11829

select 
    row_to_json(q)
from (
    select 
        true is_valid,
        false is_authorized
) q

Upvotes: 1

Related Questions