Use trigger to synchronize columns with fields in json column on insert or update

Question

I'm a bit of a database/postgres beginner,so bear with me.
If I have a table, something like this.

CREATE TABLE testy (
    id INTEGER REFERENCES other_table,
    name varchar(128) PRIMARY KEY,
    json JSONB NOT NULL
);

I'm looking to create a trigger before insert or update that will set the columns id and name to the values of fields with the same names in json.

So for example if testy contained the below and UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1 was called.

id | name | json
---+------+-----
 1 | "jim"| {"id":1,"name":"jim"}

The desired result would be

id | name | json
---+------+-----
 2 | "jim"| {"id":2,"name":"jim"}

I wish to make this fairly generic so the column names do not need to be hard coded. Setting the column to NULL if the corresponding json field does not exist is fine. So far I have

CREATE TABLE testy_index (
    id INTEGER PRIMARY KEY
);

INSERT INTO testy_index VALUES (1);
INSERT INTO testy_index VALUES (2);
INSERT INTO testy_index VALUES (3);

CREATE TABLE testy (
    id INTEGER REFERENCES testy_index,
    json JSONB NOT NULL
);

CREATE UNIQUE INDEX testy_id ON testy((json->>'id'));

CREATE OR REPLACE FUNCTION json_fn() RETURNS TRIGGER AS $testy$
    DECLARE
        roow RECORD;
    BEGIN
        FOR roow IN 
            SELECT column_name FROM information_schema.columns WHERE table_name = 'testy'
        LOOP
            NEW.roow.column_name = (NEW.json->>roow.column_name);
        END LOOP;
    END;
$testy$ LANGUAGE plpgsql;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Which doesn't work as you can't use roow.column_name that flexibly. I've tried playing around with EXECUTE with no success, although it's possible I'm just not doing it right.

Any help would be greatly appreciated!!

EDIT: The motivation for this is so that foreign key constraints can be placed on something that behaves as a json field.

EDIT: plv8 is great. Used a modified version of @Daniel Vérité 's answer so that columns not represented as fields in json will be nulled

CREATE OR REPLACE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for(var col in NEW){
      if(col == 'json'){
        continue;
      }
      if(col in obj){
        NEW[col]=obj[col];
      }else{
        NEW[col]=null;
      }
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Answer 1

Actually, this is all you need:

NEW := jsonb_populate_record(NEW, NEW.json);

The manual (since pg 13):

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

Expands the top-level JSON object to a row having the composite type of the base argument. The JSON object is scanned for fields whose names match column names of the output row type, and their values are inserted into those columns of the output. (Fields that do not correspond to any output column name are ignored.) In typical use, the value of base is just NULL, which means that any output columns that do not match any object field will be filled with nulls. However, if base isn't NULL then the values it contains will be used for unmatched columns.

That the row provided as first argument retains all values not overwritten (no matching key in the json value) was undocumented before Postgres 13.

If "undocumented" is too uncertain for you, use the hstore operator #= doing exactly the same.

NEW := (NEW #= hstore(jsonb_populate_record(NEW, NEW.json)));

The hstore module should be installed in most systems anyway. Instructions:

• Assign to NEW by key in a Postgres trigger

Either solution can be derived from:

• How to set value of composite variable field using dynamic SQL

One thing to note - you wrote:

Setting the column to NULL if the corresponding json field does not exist is fine.

This retains all values with no matching key in the JSON value, which should be even better.

Function code

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
   RETURN NEW;
END
$func$;

Everything else in your setup looks right, just add a PK to testy:

CREATE TABLE testy (
    id   int   PRIMARY KEY REFERENCES testy_index
  , data jsonb NOT NULL
);

Tested in pg 9.4 and it works for me as advertised.

Set other columns to NULL

As per comment:

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _j jsonb := NEW.json;  -- remember the json value
BEGIN
   NEW := jsonb_populate_record(NULL::testy, _j);
   NEW.json := _j;   -- reassign
   RETURN NEW;
END
$func$;

Obviously, you need to make sure that the column name or your jsonb column does not appear as key name in the JSON value. And I wouldn't use json as column name, since it's a basic data type name and that can get confusing.

Answer 2

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name.

See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime.

Personally, I'd suggest a simpler solution with the plv8 language.

CREATE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for (var key in obj) {
    NEW[key]=obj[key];
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

---

Otherwise, you may consider if your implementation is not simplified by moving the json field out of this table and into a separate table, assuming it's still compatible with your general intention.

Answer 3

I put in a little time to try and develop an answer for this question which may fit your needs, but since I don't have detailed criteria, it may not be perfect. Hopefully, though, it is close enough so that you can manipulate to meet your design needs.

Initial assumptions

To begin, I had to make a few initial assumptions to design the algorithm.

1) When using this function, you have access to the name of the table on which you are doing the simultaneous json update and your 'foreign key' column updates. It needs to be either a variable (as I've done) or it needs to be hard coded into each instance of the function separately.

2) Based on your originally specified UPDATE query, I observed that you had designed it as UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1, indicating to me that you have somewhere else in your application found a way to obtain your predicate condition, WHERE id = 1. So, this will also serve as an input to the function.

3) If this is meant to be used as a single function which can be applied across many tables, enhancing its re-usability, then you must have the relevant json column's name be identical on each table. Otherwise you have to do some extra work to look at the data type of the columns, and a few other cases I can think of where this might go wrong. Just name all of these columns as json_field and you'll be fine.

Without further ado, on to the function.

Function definition

CREATE OR REPLACE FUNCTION json_prop(json_entry json, table_update text, where_clause text)  
RETURNS void AS                                 
$func$
DECLARE
   sql text := 'UPDATE ';
   colname_row RECORD;
BEGIN

  sql := sql || table_update || ' SET ';

  FOR colname_row IN 
  (SELECT col_tab.column_name FROM 
  (SELECT column_name FROM information_schema.columns WHERE table_name = table_update)
  AS col_tab 
  WHERE (json_entry->column_name) IS NOT NULL) 
  LOOP
    sql := sql || colname_row.column_name::text || ' = ''' || ((json_entry)->>(colname_row.column_name::text)) || ''', ';
  END LOOP;

  sql := sql || 'json_field = ''' || json_entry || ''' ';
  sql := sql || where_clause || ';';

   EXECUTE sql;
END
$func$ LANGUAGE plpgsql;

So, in brief, the function takes three input arguments, and uses these to generate a dynamic SQL statement, which it then executes.

Inputs

json_entry - Exactly what you think it is. The json entry you want to update with.

table_update - The target table you want to update.

where_clause - As I mentioned above, since I made the assumption based on your description that you had pre-established predicates, that entry goes here.

Operation

The function searches for any columns in the table table_update, searching for any columns whose names match keys in the json_entry field, by performing the sub-select SELECT column_name FROM information_schema.columns WHERE table_name = table_update.

For any json key which matches a column name, that column name is returned by the outer select SELECT col_tab.column_name FROM ... AS col_tab WHERE (json_entry->column_name) IS NOT NULL).

The FOR loop iterates over each of these matching column names, adding on to the dynamic SQL statement the necessary info for updating the relevant column data.

NOTE that 'extraneous' fields have been ignored. That is, if there is a json key for which there is not a matching column name, or there is a column name which is not in the json_entry input, these fields are ignored.

Calling the function

The function can be invoked by simply calling

SELECT * FROM json_prop('{"id":2,"name":"james"}'::json, 'testy', 'WHERE id = 1');

Possible modifications

Again, this may not be perfect for your needs. You mentioned that you are considering using it as a trigger, so then you'd need to RETURN a trigger instead and set up your triggers. You may not like that I ignored 'extraneous' fields, and you may want to make columns NULL or flag an error in these cases? Maybe I made the wrong assumption about access to predicates?

This certainly isn't a perfect functional implementation, but it will probably be enough for you to modify it to meet your needs.

Best of luck!