updating a row of a table using data from json and working with json variables

Question

Consider a table:
id is a integer, name is varchar and age is also an integer

id |name | age
______________
1. | one | 12
--------------
2. | two | 15
--------------
3. | thre| 16
--------------

In my node code, I have a json object:

const jsonFromNode = {
  id: 1,
  name: "new_one",
  age: 12
}

I'm using a query builder, knex , and I'd like to use this json directly to update the row in the table, I was thinking something like this in node(I'm not sure if this will work though, and the following is in pseudocode):

const string = `
do $$
  DECLARE
    myjson JSON;
  BEGIN
    myjson := '${jsonFromNode}';

    -- some psql method/function/procedure to update
    -- the row where id=1 and age= 12
    SomePlPgsqlFunctionOrStepOfProcedures(myjson); //update by passing json
  END;
$$ language 'plpgsql';
`

So, the major problems are:

Is this possible that my idea will work?
What is the syntax used to fetch value of data in my json variable. E.g. x = {'a': 'b'} is my variable in psql above, how do I do something like this in psql: y = x['b']
What are the steps or functions that would replace: SomePlPgsqlFunctionOrStepOfProcedures above

snakecharmerb · Accepted Answer · 2021-06-20 17:22:10Z

2

You don't really need a function or procedure, you can do the insert in a single statement, using the jsonb_populate_record function (docs).

Here's how you would do it in the psql console:

test# -- assign the json to a variable
test# \set j  '{"id": 1, "name": "new_one", "age": 12 }'
test# insert into ages (id, name, age) select id, name, age from jsonb_populate_record(null::ages, :'j'::jsonb);
INSERT 0 1
test# table ages;
 id │  name   │ age 
════╪═════════╪═════
  1 │ new_one │  12

To answer the actual question: this function does the trick.

CREATE OR REPLACE FUNCTION json_update (_data jsonb) RETURNS void AS $$
DECLARE
    _id int;
    _name text;
    _age int;
BEGIN
    SELECT id, name, age INTO _id, _name, _age 
      FROM jsonb_populate_record(null::ages, _data);
    UPDATE ages SET name = _name, age = _age WHERE id = _id;
END;
$$ LANGUAGE plpgsql;

Or this version, which uses a single record variable rather than one for each column:

CREATE OR REPLACE FUNCTION json_update (_data jsonb) RETURNS void AS $$
DECLARE
    _myrecord record;
BEGIN
    SELECT id, name, age INTO _myrecord 
      FROM jsonb_populate_record(null::ages, _data);
    UPDATE ages SET name = _myrecord.name, age = _myrecord.age WHERE id = _myrecord.id;
END;
$$ LANGUAGE plpgsql;

edited Jun 20, 2021 at 17:22

answered Jun 20, 2021 at 15:44

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

5 Comments

BumbleBee Over a year ago

@snakecharerb , thanks for the answer. But, I actually have to use some if elses and this is the part I was having confusion with, so I did have to use procedures. And there were some insert examples I found, but, I didn't find them for update. Could you please mention for update instead of insert?(in plpgsql if possible)

BumbleBee Over a year ago

above you pass data as jsonb, can I write (data JSON) instead?

snakecharmerb Over a year ago

Yes - but note you need to use json_populate_record instead of jsonb_populate_record.

BumbleBee Over a year ago

Thanks, that answers my question. But, if you could, please consider this one scenerio: suppose name was something that may not be available in json, i.e. users sending in this json data, may omit it, then, out _name would possibly(?) be null, but we'd not like to add that data in there, instead we would like 'M_NAME' instead, how do we deal with that?

snakecharmerb Over a year ago

You can use the COALESCE function to provides defaults for nulls: ... SET name = COALESCE(_myrecord.name, 'M_NAME'), ...

Collectives™ on Stack Overflow

updating a row of a table using data from json and working with json variables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related