CODEWITHSUNDEEP
postgresqljsonb
Denis Ivanov
Denis Ivanov

Reputation: 23

Postgres jsonb get by dynamic path

Is it possible to do something like this in Postgres:

do $$
declare
  v_key text;
  v_json jsonb;
begin
  v_key := 'id';
  v_json := jsonb_build_object(
    'id', jsonb_build_object('nest_id',1)
  );
  raise notice '%', v_json #> '{'||v_key||'}'->>'nest_id';
end$$

ERROR: operator does not exist: jsonb #> text
No operator matches the given name and argument type(s). You might need to add explicit type casts.

Upvotes: 2

Views: 1814

Answers (1)

klin
klin

Reputation: 121574

Right operand type of the operator #> is text array. Use array[...] notation:

raise notice '%', v_json #> array[v_key] ->> 'nest_id';

or explicitly cast a formatted array literal:

raise notice '%', v_json #> ('{'||v_key||'}')::text[] ->> 'nest_id';
-- or nicer
raise notice '%', v_json #> format('{%s}', v_key)::text[] ->> 'nest_id';

Upvotes: 1

Related Questions