-> and ->> and #> and #>> (JSON subvalue operators)
Purpose: Read a JSON value at a specified path. The > variants return a json or jsonb value, according to the data type of the input. And the >> variants return a text value. The #> and #>> variants differ from -> and ->> variants in how the path is specified.
The -> operator
Purpose: Read the value specified by a one-step path returning it as a json or jsonb value.
Signature For the jsonb overload:
input values: jsonb -> [int | text] [ -> [int | text] ]*
return value: jsonb
Notes: The -> operator requires that the JSON value is an object or an array. Key is a SQL value. When key is a SQL text value, it reads the JSON value of the key-value pair with that key from an object. When key is a SQL integer value, it reads the JSON value at that index key from an array. If the input JSON value is json, then the output JSON value is json, and correspondingly if the input JSON value is jsonb.
Reading a key value:
do $body$
declare
j constant jsonb := '{"a": 1, "b": {"x": 1, "y": 19}, "c": true}';
jsub constant jsonb := j -> 'b';
expected_jsub constant jsonb := '{"x": 1, "y": 19}';
begin
assert
jsub = expected_jsub,
'unexpected';
end;
$body$;
Reading an array value. (The first value in an array has the index 0.)
do $body$
declare
j constant jsonb := '["a", "b", "c", "d"]';
j_first constant jsonb := j -> 0;
expected_first constant jsonb := '"a"';
begin
assert
j_first = expected_first,
'unexpected';
end;
$body$;
The #> operator
Purpose: Read the value specified by a multi-step path returning it as a json or jsonb value.
Signature for the jsonb overload:
input value: jsonb #> text[]
return value: jsonb
Notes: An arbitrarily deeply located JSON subvalue is identified by its path from the topmost JSON value. In general, a path is specified by a mixture of keys for object subvalues and index values for array subvalues.
Consider this JSON value:
[
1,
{
"x": [
1,
true,
{"a": "cat", "b": "dog"},
3.14159
],
"y": true
},
42
]
-
At the topmost level of decomposition, it's an array of three subvalues.
-
At the second level of decomposition, the second array subvalue (i.e. the value with the index of
1) is an object with two key-value pairs called "x" and "y". -
At the third level of decomposition, the subvalue for the key "x" is an array of subvalues.
-
At the fourth level of decomposition, the third array subvalue (i.e. the value with the index of
2) is an object with two key-value pairs called "a" and "b". -
And at the fifth level of decomposition, the subvalue for key "b" is the primitive string value "dog".
This, therefore, is the path to the primitive JSON string value "dog":
-> 1 -> 'x' -> 2 -> 'b'
(Recall that array value indexing starts at zero.)
The #> operator is a convenient syntax sugar shorthand for specifying a long path compactly, thus:
#> array['1', 'x', '2', 'b']::text[]
Notice that with the -> operator, integers must be presented as such (so that '1' rather than 1 would silently read out NULL. However, with the #> operator, integers must be presented as convertible text values because all the values in a SQL array must have the same data type.
The PL/pgSQL ASSERT confirms that both the -> path specification and the #> path specification produce the same result, thus:
do $body$
declare
j constant jsonb := '
[
1,
{
"x": [
1,
true,
{"a": "cat", "b": "dog"},
3.14159
],
"y": true
},
42
]';
one constant int := 1;
two constant int := 2;
x constant text := 'x';
b constant text := 'b';
one_t constant text := '1';
two_t constant text := '2';
jsub_1 constant jsonb := j -> one -> x -> two -> b;
jsub_2 constant jsonb := j #> array[one_t, x, two_t, b];
expected_jsub constant jsonb := '"dog"';
begin
assert
(jsub_1 = expected_jsub) and
(jsub_2 = expected_jsub),
'unexpected';
end;
$body$;
The paths are written using PL/pgSQL variables so that, as a pedagogic device, the data types are explicit.
The ->> and #>> operators
Purpose: Read the specified JSON value as a text value.
Signatures For the jsonb overloads:
input values: jsonb ->> [int | text] [ -> [int | text] ]*
return value: text
and:
input value: jsonb #>> text[]
return value: text
Notes: The -> operator returns a JSON object. When the targeted value is compound, the ->> operator returns the ::text typecast of the value. But when the targeted value is primitive, the ->> operator returns the value itself, typecast to a text value. In particular; a JSON number value is returned as the ::text typecast of that value (for example '4.2'), allowing it to be trivially ::numeric typecast back to what it actually is; a JSON boolean value is returned as the ::text typecast of that value ('TRUE' or 'FALSE'), allowing it to be trivially ::boolean typecast back to what it actually is; a JSON string value is return as is as a text value; and a JSON null value is returned as a genuine SQL NULL so that the IS NULL test is TRUE.
The difference in semantics between the -> operator and the ->> operator is vividly illustrated (as promised above) by targeting this primitive JSON string subvalue:
"\"First line\"\n\"second line\""
from the JSON value in which it is embedded. For example, here it is the value of the key "a" in a JSON object:
do $body$
declare
j constant jsonb := '{"a": "\"First line\"\n\"second line\""}'::jsonb;
a_value_j constant jsonb := j -> 'a';
a_value_t constant text := j ->> 'a';
expected_a_value_j constant jsonb :=
'"\"First line\"\n\"second line\""'::jsonb;
expected_a_value_t constant text := '"First line"
"second line"';
begin
assert
(a_value_j = expected_a_value_j) and
(a_value_t = expected_a_value_t),
'unexpected';
end;
$body$;
Understanding the difference between the -> operator and the ->> operator completely informs the understanding of the difference between the #> operator and the #>> operator.
The >> variant (both for -> vs ->> and for #> vs #>>) is interesting mainly when the denoted subvalue is a primitive value (as the example above showed for a primitive string value). If you read such a subvalue, it's most likely that you'll want to cast it to a value of the appropriate SQL data type, numeric, text, or boolean. You might use your understanding of a value's purpose (for example "quantity ordered" or "product SKU") to cast it to, say, an int value or a constrained text type like varchar(30). In contrast, if you read a compound subvalue, it's most likely that you'll want it as a genuine json or jsonb value.
Summary: -> versus ->> and #> versus #>>
- Each of the
->and#>operators returns a genuine JSON value. When the input isjson, the return value isjson. And when the input isjsonb, the return value isjsonb. - Each of the
->>and#>>operators returns a genuinetextvalue, both when the input isjson, and when the input isjsonb. - If the value that the path denotes is a compound JSON value, then the
>>variant returns thetextrepresentation of the JSON value, as specified by RFC 7159. (The designers of the PostgreSQL functionality had no other feasible choice.) But if the JSON value that the path denotes is primitive, then the >> variant produces the text representation of the value itself. It turns out that the text representation of a primitive JSON value and the text representation of the value itself differ only for a JSON string—exemplified by "a" versus a.
The following ASSERT tests all these rules:
do $body$
declare
ta constant text := 'a';
tn constant numeric := -1.7;
ti constant numeric := 42;
tb constant boolean := true;
t2 constant text := '["a", -1.7, 42, true, null]';
j1 constant jsonb := '"a"';
j2 constant jsonb := t2::jsonb;
j3 constant jsonb := '{"p": 1, "q": ["a", -1.7, 42, true, null]}';
begin
assert
(j2 -> 0) = j1 and
(j2 ->> 0) = ta and
(j2 ->> 1)::numeric = tn and
(j2 ->> 2)::int = ti and
(j2 ->> 3)::boolean = tb and
(j2 ->> 4) is null and
(j3 #> array['q', '0']) = j1 and
(j3 #>> array['q', '0']) = ta and
(j3 -> 'q') = j2 and
(j3 ->> 'q') = t2
,
'unexpected';
end;
$body$;