PostgreSQL Question posted in
The official documentation can be found here.

Difference between jsonb type cast and to_jsonb in postgresql

Why do these two queries give different result in postgresql ?

select '{"items":["foo", "bar"]}'::jsonb->'items';

select to_jsonb('{"items":["foo", "bar"]}'::text)->'items';

the first one returns: ["foo", "bar"] and the second one returns [NULL]

Tags:

2

Answers


  1. 0

    The documentation for to_jsonb() says this (emphasis mine):

    Converts any SQL value to json or jsonb. Arrays and composites are converted recursively to arrays and objects (multidimensional arrays become arrays of arrays in JSON). Otherwise, if there is a cast from the SQL data type to json, the cast function will be used to perform the conversion; otherwise, a scalar JSON value is produced. For any scalar other than a number, a Boolean, or a null value, the text representation will be used, with escaping as necessary to make it a valid JSON string value.

    In other words, it isn’t parsing JSON from string, it’s creating a JSON string from your text.

    select 
    to_jsonb('{"items":["foo", "bar"]}'::text),
    to_jsonb('{"items":["foo", "bar"]}'::text)->'items';
    to_jsonb ?column?
    "{"items":["foo", "bar"]}"
    Login or Signup to reply.
  2. 0
    • First one is assignment, second one is conversion.
    • First one assigns a type to an unknown constant, the second one converts a constant of an already known text type, to another type.

    PostgreSQL treats single-quoted constants as type unknown. When you add ::jsonb, it verifies if the constant is a valid jsonb literal and assigns the type.

    It already told you exactly that, which is why you had to add ::text before passing the constant to to_jsonb():

    select to_jsonb('{"items":["foo", "bar"]}');

    ERROR:  could not determine polymorphic type because input has type unknown

    And once you added ::text, to_jsonb() mapped the whole thing from a plain PostgreSQL text type to JSON internal string type, ignoring what’s inside it. -> returned null because the value is now a lonely JSON string, not an object with keys in it, so the path ->'items' isn’t valid.
    The only valid path in there is no path at all: #>>'{}' (that’s an empty array of keys that forms a path with no steps)

    select to_jsonb('{"items":["foo", "bar"]}'::text) #>> '{}';
    ?column?
    "{"items":["foo", "bar"]}"

    Which is how you can get back your text-type value back out of that JSON.

    The clever thing about to_jsonb() is how you can automagically map out your tables to jsonb:

    create table test(a text,b boolean, c numeric);
insert into test values ('abc', true, 123.45);

select to_jsonb(test) from test;
    to_jsonb
    {"a": "abc", "b": true, "c": 123.45}

    Which the cast won’t do:

    select test::jsonb from test;

    ERROR:  cannot cast type test to jsonb
LINE 1: select test::jsonb from test;
                   ^
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search