JSON literals

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

This section shows that the literal for both a jsonb value and a json value, as these are used both in SQL statements and in PL/pgSQL code, is the enquoted and appropriately typecast RFC 7159-compliant text value that represents the JSON value.

The mutual relationship between a JSON value and its ::text typecast is an instance of the general rule that governs the mutual relationship between a value of any data type and its ::text typecast. This general rule is explained in the section The text typecast of a value, the literal for that value, and how they are related.

This DO block follows, for jsonb, the same pattern that is used in that section for a variety of different data types. See also the section ::jsonb and ::json and ::text (typecast).

create type t as (f1 int, f2 text, f3 boolean, f4 text[]);

do $body$
declare
  v1 constant int     := 42;
  v2 constant text    := 'a';
  v3 constant boolean := true;
  v4 constant text[]  := array['x', 'y', 'z'];

  v5 constant t := (v1, v2, v3, v4);

  original   constant jsonb  not null := to_jsonb(v5);
  text_cast  constant text   not null := original::text;
  recreated  constant jsonb  not null := text_cast::jsonb;
begin
  assert
    (recreated = original),
  'assert failed';
  raise info 'jsonb: %', text_cast;
end;
$body$;

See the account of the to_jsonb() function. The DO block produces this output (after manually stripping the "INFO:" prompt):

jsonb: {"f1": 42, "f2": "a", "f3": true, "f4": ["x", "y", "z"]}

It shows that the "non-lossy round trip rule" holds here too:

  • jsonb value to text typecast and back to jsonb value

And it shows that the ::text typecast of a jsonb value that has been constructed bottom-up from SQL values conforms to RFC 7159.

The next block follows the same pattern for json. However, the ASSERT must be written differently because the = operator has no overload for json.

do $body$
declare
  v1 constant int     := 42;
  v2 constant text    := 'a';
  v3 constant boolean := true;
  v4 constant text[]  := array['x', 'y', 'z'];

  v5 constant t := (v1, v2, v3, v4);

  original   constant json  not null := to_json(v5)::jsonb;
  text_cast  constant text  not null := original::text;
  recreated  constant json  not null := text_cast::json;
begin
  assert
    (recreated::jsonb = original::jsonb),
  'assert failed';
  raise info 'json: %', text_cast;
end;
$body$;

The output is the same as the DO block for jsonb produces. It shows that the same "non-lossy round trip" rule holds for a json value too.

Recall (see the Synopsis section) that a json value is stored as a text value, annotated with the fact of what the data type is, and that whitespace in such a value is semantically insignificant. (The jsonb data type stores a parsed representation of the document hierarchy of subvalues in an appropriate internal format.) This has no effect on the reliability of the "non-lossy round trip" rule. However, it means that a round trip json value to jsonb value to json value might be lossy because, unless the json value happens to use whitespace just as the ::text typecast of a jsonb value does, the recreated json value will have different whitespace from the original json value.