CODEWITHSUNDEEP
postgresql
user2741831
user2741831

Reputation: 2388

How can I read keys from a json variable in postgres

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?

Upvotes: 0

Views: 70

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions