Postgres jsonb get by dynamic path

Question

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.

'{'||v_key||'}'::jsonb

user330315
– user330315

2016-09-07 10:17:36 +00:00
Commented Sep 7, 2016 at 10:17 — user330315
– user330315, Commented Sep 7, 2016 at 10:17

klin · Accepted Answer · 2021-10-26 10:36:09Z

1

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';

edited Oct 26, 2021 at 10:36

answered Sep 7, 2016 at 10:29

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres jsonb get by dynamic path

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related