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!!!

At least give the reason why to close?

Code Guru
– Code Guru

2018-04-21 17:13:32 +00:00
Commented Apr 21, 2018 at 17:13 — Code Guru
– Code Guru, Commented Apr 21, 2018 at 17:13

klin · Accepted Answer · 2018-04-21 18:01:39Z

2

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

edited Apr 21, 2018 at 18:01

answered Apr 21, 2018 at 17:48

klin

123k15 gold badges241 silver badges263 bronze badges

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

2 Comments

Code Guru Over a year ago

not working.. raise notice '%,%',vote_to,poll_result::json #>> array[vote_to]; is printing NOTICE: "yes",<NULL>

Code Guru Over a year ago

yes!! this is working.. the poll_result is from database table whose type is json. Then why I need to trim "

Collectives™ on Stack Overflow

Access json objects using variables as keys in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related