Postgres jsonb get by dynamic path

1
votes

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.

1
postgresqljsonb
'{'||v_key||'}'::jsonb - a_horse_with_no_name

1 Answers

0
votes

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

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

or format array literal:

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