PostgreSQL json_value() boolean behavior

Question

I find it a little weird that

select json_value(jsonb '[true]','$[0]' returning jsonb)::text

returns true, but

select json_value(jsonb '[true]','$[0]')::text

returns t.

Is that a bug in PostgreSQL? Using PostgreSQL 17.4.

Answer 1

If you're only looking to get a true instead of a t while avoiding quoted strings on output from the jsonb value, you can switch from json_value() to json_query() that offers an omit quotes clause:

select json_query(v,'$' returning text omit quotes)
from(values(jsonb 'true'),('"abc"'))_(v);

true
abc

Or, as you pointed out, peel the jsonb off of a json_value(..returning jsonb) using a text-yielding root accessor:

select json_value(v,'$' returning jsonb)#>>'{}'
from(values(jsonb 'true'),('"abc"'))_(v);

If you don't really need the added features they offer over a simple accessor, you can probably get away with #>>{} alone:

select v#>>'{}'
from(values(jsonb 'true'),('"abc"'))_(v);

The json_query() differs from json_value() in how it treats non-scalars but the former seems to slightly outperform the latter. Here's a test on 1.6M loops of the three queries above:

v#>>'{}'	00:00:03.947142
json_query(..omit quotes)	00:00:07.441133
json_value()#>>'{}'	00:00:08.356777

---

Why it's true for jsonb, t for boolean

PostgreSQL type boolean accepts many different types of text constants on input, but it defaults to t/f:
_{demo at db<>fiddle}

value literal	pg_input_is_valid(v,'boolean')	is valid jsonb?	pg_input_error_info(v,'jsonb')
0	t	t	(,,,)
1	t	t	(,,,)
f	t	f	("invalid input syntax for type json","Token ""f"" is invalid.",,22P02)
fa	t	f	("invalid input syntax for type json","Token ""fa"" is invalid.",,22P02)
fal	t	f	("invalid input syntax for type json","Token ""fal"" is invalid.",,22P02)
fals	t	f	("invalid input syntax for type json","Token ""fals"" is invalid.",,22P02)
false	t	t	(,,,)
FaLsE	t	f	("invalid input syntax for type json","Token ""FaLsE"" is invalid.",,22P02)
' FaLsE '	t	f	("invalid input syntax for type json","Token ""FaLsE"" is invalid.",,22P02)
t	t	f	("invalid input syntax for type json","Token ""t"" is invalid.",,22P02)
tr	t	f	("invalid input syntax for type json","Token ""tr"" is invalid.",,22P02)
tru	t	f	("invalid input syntax for type json","Token ""tru"" is invalid.",,22P02)
true	t	t	(,,,)
of	t	f	("invalid input syntax for type json","Token ""of"" is invalid.",,22P02)
off	t	f	("invalid input syntax for type json","Token ""off"" is invalid.",,22P02)
on	t	f	("invalid input syntax for type json","Token ""on"" is invalid.",,22P02)

As shown above, JSON scalar type boolean accepts only true or false and that's what json_value() outputs when you request a jsonb result. It's also in the doc already mentioned by @Adrian Klaver:

Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types

JSON primitive type	PostgreSQL type	Notes
string	text	\u0000 is disallowed, as are Unicode escapes representing characters not available in the database encoding
number	numeric	NaN and infinity values are disallowed
boolean	boolean	Only lowercase true and false spellings are accepted
null	(none)	SQL NULL is a different concept

---

"Is that a bug in PostgreSQL?"

It's not, it's intentional. You can check the source to see what's going on exactly: the postgres/src/backend/executor/execExprInterp.c:4807 shows you what happens when you use returning jsonb or returning json:

if (jsexpr->returning->typid == JSONOID ||
    jsexpr->returning->typid == JSONBOID)
{
    val_string = DatumGetCString(DirectFunctionCall1(jsonb_out,
                                                     JsonbPGetDatum(JsonbValueToJsonb(jbv))));
}

This hops through jsonb type's jsonb_out function, JsonbToCString and JsonbToCStringWorker to jsonb_put_escaped_value which just types out the full-length lowercase true or false:

case jbvBool:
    if (scalarVal->val.boolean)
        appendBinaryStringInfo(out, "true", 4);
    else
        appendBinaryStringInfo(out, "false", 5);
    break;

If you use returning text or skip it entirely, letting Postgres use that as default, json_value() instead puts you through ExecGetJsonValueItemString, which uses the (SQL) boolean's boolout function, yielding the default t or f:

Datum
boolout(PG_FUNCTION_ARGS)
{
    bool        b = PG_GETARG_BOOL(0);
    char       *result = (char *) palloc(2);

    result[0] = (b) ? 't' : 'f';
    result[1] = '\0';
    PG_RETURN_CSTRING(result);
}