Database function which updates json

Question

In a Postgres DB I have a table (Users) which defines users. It is like

|ID|                    meta                               |
|01|  {"email" : "test1@gmail.com","importance" : "High"}  |
|02|  {"email" : "test2@gmail.com","importance" : "Medium"}|

As you can see the 'meta' column contains json, for a specific reason. (not changeable)

I would like to create a function, which accepts 3 parameters:

An email (inc_email)
A Key (inc_key)
A Value (inc_value)

It would then search for the record which has a meta containing the e-mail, and it would add the new key-values to the json. But! If the value is empty, it would delete the key-values if they are already there.

Example:

function('test1@gmail.com','security','true')

would result:

|ID|                    meta                                                  |
|01|  {"email" : "test1@gmail.com","importance" : "High", "security":"true"}  |

Then:

function('test1@gmail.com','security','')

would result:

|ID|                    meta                             |
|01|  {"email" : "test1@gmail.com","importance" : "High"}|

So far I got to the point where I search the specific record, and I give back the json result, but I am not sure what is the best way to implement this json modification.

declare
 ret json;
begin
  select array_to_json(array_agg(u.meta)) into ret
   from Users u
    where meta::text LIKE ('%"email" : "' || inc_email || '%');
  return ret;
end;

Laurenz Albe · Accepted Answer · 2017-01-25T14:34:56.803

2

CREATE OR REPLACE FUNCTION set_value(
   inc_email text,
   inc_key text,
   inc_value text
) RETURNS void LANGUAGE plpgsql AS
$$DECLARE
   r_id integer;
   r_meta json;
BEGIN
   /* loop through all matching rows */
   FOR r_id, r_meta IN
      SELECT id, meta
         FROM users
         WHERE meta::jsonb @> ('{ "email": "' || inc_email || '" }')::jsonb
   LOOP
      /* add, update or delete the key/value pair */
      r_meta := CASE
                   WHEN inc_value IS NULL
                      OR inc_value = ''
                   THEN
                      /* remove key/value pair */
                      (r_meta::jsonb - inc_key)::json
                   ELSE
                      /* add or replace key/value pair */
                      jsonb_set(
                         r_meta::jsonb,
                         ARRAY[inc_key],
                         ('"' || inc_value || '"')::jsonb
                      )::json
                END;

      /* write the result back to the database */
      UPDATE users
         SET meta = r_meta
         WHERE id = r_id;
   END LOOP;
END;$$;

edited Jan 25 '17 at 14:34

answered Jan 24 '17 at 15:00

Laurenz Albe

209,280
17
206
263

For me it seems he is not sure only about the 'modify the jsonb' part. – Mr.Fireman Jan 24 '17 at 15:02
That's correct, what I am interested in is the json update as it doesn't seem a normal 'update' as I don't want to concatenate any value at the end of the data, I would like to expand the inner of the json with the new ones. I wouldn't do a for loop anyway, as every e-mail is unique. – Macskasztorik Jan 24 '17 at 15:05
1

I would go with this article first, it has some really detailed explanation: http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype – Mr.Fireman Jan 24 '17 at 15:24
Ok, I give in and write the code for you (although there is nothing interesting or tricky about it). – Laurenz Albe Jan 25 '17 at 09:13
Thank you Laurenz, much appreciated, but unfortunately jsonb_set is only working with with jsonb datatype, not with json. – Macskasztorik Jan 25 '17 at 10:35
1

Where's the problem? Cast to `jsonb` and then back to `json`. – Laurenz Albe Jan 25 '17 at 10:43
Wow I didn't about this casting option! Thank you! – Macskasztorik Jan 25 '17 at 16:47

Database function which updates json

1 Answers1