Access json objects using variables as keys in postgresql

Question

I have a json poll_result

{"total_votes":1,"yes":1,"no":0}

and I have a variable that has the key

vote_to = _poll_response::json#>>'{vote}';

and that returns the "yes".

Now I want to access poll_result json based on the vote_to variable's value, so I am trying

raise notice '%',poll_result::json#>>'{||vote_to||}';

but this is printing <NULL>. Even I have tried like

raise notice '%,%',poll_result,poll_result::json#>>'{''||vote_to||''}';

but the result is same <NULL>.

Please help!!!

Answer 1

See the example on how to use a variable with ->> and #>> operators:

do $$
declare
    pool_result json = '{"total_votes":1,"yes":1,"no":0}';
    vote_to text = 'yes';
begin
    raise notice 'yes: %', pool_result ->> vote_to;
    -- or
    raise notice 'yes: %', pool_result #>> array[vote_to]; 
end;
$$

NOTICE:  yes: 1
NOTICE:  yes: 1

If the the value of the variable is in double quotes you should trim them:

do $$
declare
    pool_result json = '{"total_votes":1,"yes":1,"no":0}';
    vote_to text = '"yes"';
begin
    raise notice 'yes: %', pool_result ->> trim(vote_to, '"');
    -- or
    raise notice 'yes: %', pool_result #>> array[trim(vote_to, '"')]; 
end;
$$