jsonb_extract_path() and json_extract_path()
Purpose: Provide the identical functionality to the #>
operator.
Signature For the jsonb
variant:
input value: jsonb, VARIADIC text
return value: jsonb
The invocation of #>
can be mechanically transformed to use jsonb_extract_path()
by these steps:
- Add the function invocation with its required parentheses.
- Replace
#>
with a comma. - Write the path as a comma-separated list of terms, where both integer array indexes and key-value keys are presented as
text
values, taking advantage of the fact that the function is variadic).
This DO
block shows the invocation of #>
and jsonb_extract_path()
vertically to help visual comparison.
do $body$
declare
j constant jsonb :=
'[1, {"x": [1, true, {"a": "cat", "b": "dog"}, 3.14159], "y": true}, 42]';
jsub_1 constant jsonb := j #> array['1', 'x', '2', 'b'];
jsub_2 constant jsonb := jsonb_extract_path(j, '1', 'x', '2', 'b');
expected_jsub constant jsonb := '"dog"';
begin
assert
(jsub_1 = expected_jsub) and
(jsub_2 = expected_jsub),
'unexpected';
end;
$body$;
Notice that even though jsonb_extract_path()
is variadic, each step that defines the path must be presented as a convertible SQL text
, even when its meaning is a properly expressed by a SQL integer
.
The function form is more verbose than the operator form. Moreover, the fact that the function is variadic makes it impossible to invoke it statically (in PL/pgSQL code) when the path length isn't known until run time. There is, therefore, no reason to prefer the function form to the operator form.