2

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.

12
  • 1
    No, just difference in representation. When you do returning jsonb you are asking for JSON output which per JSON Types is: Only lowercase true and false spellings are accepted. In second case you are asking for Postgres/SQL and per Boolean Type is: The datatype output function for type boolean always emits either t or f. The important part is they represent the same thing. Commented Mar 4 at 16:08
  • I would suggest looking at JSON Types: Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types for other differences. Commented Mar 4 at 16:18
  • Ok, is there a way to tell PostgreSQL to return the text true/false for JSON booleans instead? Commented Mar 4 at 18:13
  • @AdrianKlaver I guess the unexpected thing here is that json_value defaults to using the "datatype output function", not the ::text conversion (of either 'true'::jsonb::text or true::bool::text). Commented Mar 4 at 19:09
  • 1
    Peeling off the jsonb wrapping with #>>'{}' is quick and cheap. Commented Mar 5 at 10:44

1 Answer 1

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);
}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.