How do I insert an element into an array contained into a POSTGRESQL 9.5 jsonb field?

Question

I'm storing the data in a jsonb field called members_json in PostgreSQL 9.5 in a table called event_members_json. I want to make a trigger that everytime an insert gets done on another table call event_members inserts the new event member into the array of the jsonb field.

The field jsonb field has the following structure:

{"members": [{"user_id": 1, "last_name": "argento", "first_name": "pepe", "firebase_id": "123", "profile_pic": "storage/image.png", "is_moderator": "t"}]}

which would be the syntax to insert a new member(json object) into the members array described above??...

Thank you very much!

Use -> operator to get the "members" value, || operator to add the object to the array, and jsonb_set function to set the modified value of the "members" key. — Abelisto

Patrick Patrick · Accepted Answer · 2016-08-14T08:08:29

You need to create a trigger function that builds a jsonb object from the values being inserted and then concatenate that to the members element of the jsonb value in the other table:

CREATE FUNCTION add_jsonb_member () RETURNS trigger AS $$
BEGIN
  UPDATE event_members_json
  SET json_column = jsonb_set(json_column, '{members}', json_column->'members' ||
                              jsonb_build_object('user_id', NEW.user_id,
                                                 'last_name', NEW.last_name,
                                                 ...
                                                )
                             )
  WHERE ...;
  RETURN; -- Assuming AFTER INSERT trigger
END;
$$ LANGUAGE plpgsql;

You should call this function in an AFTER INSERT trigger on table event_members.

How do I insert an element into an array contained into a POSTGRESQL 9.5 jsonb field?

1 Answers