How can I read keys from a json variable in postgres

Question

I have json object that is structured like this

{
   item_id:"f1e00bae-40b1-4bb6-ab21-fc8b5426ad0a"
   item_amount:8978
}

now I have a function that looks something like this:

create or replace function update_item(
    update_item JSON,
    username text
)
RETURNS item
as $$
DECLARE 
inserted item;
begin
     UPDATE item
     set amount=amount+update_item.item_amount
     WHERE 
     item.owner=username
     AND
     item.id=update_item.item_id
     RETURNING * INTO inserted;

     RETURN inserted;
         
             

END $$
language plpgsql;

But it gives me this error:

missing FROM-clause entry for table "update_item"

So how can I get the required values from the JSON paramter?

user330315 · Accepted Answer · 2021-01-17 20:24:08Z

1

You can access a key's value using the ->> operator. But as everything is "text" in JSON, you will have to cast the values when you use them:

create or replace function update_item(update_item JSON, username text)
  RETURNS item
as $$
DECLARE 
  inserted item;
begin
  UPDATE item
    set amount = amount + (update_item ->> 'item_amount')::int
  WHERE item.owner = username
  AND item.id = (update_item ->> 'item_id')::uuid
  RETURNING * INTO inserted;

  RETURN inserted;
END $$
language plpgsql;

answered Jan 17, 2021 at 20:24

user330315

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

Collectives™ on Stack Overflow

How can I read keys from a json variable in postgres

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