4

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();
  • It looks like you are interested in using dynamic sql (for the column name) ... have a look at this question: stackoverflow.com/questions/12780275/… ... you may also need to use SELECT INTO in the EXECUTE statement (although not sure about that). – Joishi Bodio Jul 1 '15 at 22:33
  • BTW - why do you need to do this? As you already have shown above, you can create an index on a json key.. – Joishi Bodio Jul 1 '15 at 22:37
  • @JoishiBodio yes that's what I was looking at. But it feels like the dynamic sql doesn't support assignments like NEW.thing = other_thing. I want to do this so that I can effectively use json fields as keys. – kag0 Jul 1 '15 at 22:41
  • Your query above UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1 can easily be changed to UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE (json->>'id') = 1 and it would use the index you have shown as created .. CREATE UNIQUE INDEX testy_id ON testy((json->>'id')); I'm not sure why you need to look through the data dictionary for the column names, though - isn't that a defined fixed set? In your example you have two columns id and name .. you could have two lines setting each column appropriately (instead of loop) - how many columns does your REAL table have? – Joishi Bodio Jul 1 '15 at 22:49
  • @JoishiBodio I should have been specific, it's foreign keys I'm after. Obviously yes I could just use unique indexes like primary keys like you have shown. I want to avoid hard-coding the column names because I would like to use this trigger on a variety of tables with minimal modification. So this trigger isn't designed for any specific "real" table, the tables I'm looking at using it on have between teens and tens of columns. – kag0 Jul 1 '15 at 23:03
2

Actually, this is all you need:

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

Per documentation:

jsonb_populate_record(base anyelement, from_json jsonb)

Expands the object in from_json to a row whose columns match the record type defined by base (see note below).

What's not documented: The row provided as first argument retains all values that are not overwritten (no matching key in the json value). I see no reason why this should change, but you cannot fully rely on it unless it's documented.

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.

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:

Both solution can be derived from my answer that Daniel already referenced:

Function code

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

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. I doubt that the PLv8 function can rival performance and simplicity.

Set other columns to NULL

As per comment:

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER 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$ LANGUAGE plpgsql;

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 data type name and that can get confusing.

  • Setting the column to null if the corresponding json field does not exist would actually be preferred, it is my aim to avoid any values in the columns which are not represented also in json fields. Is there an elegant way to do that here? – kag0 Jul 4 '15 at 22:10
  • @kag0: I added a solution for that. – Erwin Brandstetter Jul 5 '15 at 9:15
3

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.

  • That does look fantastically simpler. Are there any downsides of plv8 over plpgsql? – kag0 Jul 2 '15 at 17:31
  • @kag0: see plv8 disadvantages or limitations? on S.O.? It seems to me that lack of traction is the major drawback. Database people are conservative and we're used to good old plpgsql. Most of the PL code out there is written with it and it's self-perpetuating. – Daniel Vérité Jul 3 '15 at 18:08
2

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!

  • Looks like lots of great info. I'll have to play around with this. You were correct that I wasn't planning on ignoring 'extraneous' fields, I was thinking they would be set to null, but that might be a desirable but not required feature. I was thinking trigger so that the function would run on each row, that way predicates didn't need to be assumed and the fn doesn't need to be called explicitly. Do you know if you can pass parameters to functions when using triggers? – kag0 Jul 2 '15 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.