Merge JSON Postgresql column data when performing UPDATE

Question

The scenario is as follows, I have a JSONB type column in my database where a JSON structure is saved.

What I need is that when this structure is updated, it will update only the keys that have been requested to update and maintain the existing ones. If you give a common update on this type of column it just replaces the old data with new ones, however I need to perform a MERGE.

In some other code scenarios I have used the following structure:

update tabela set coluna=(coluna || (jsonDeEntrada)::jsonb);

It works for the cases I had until today, but my new JSON structure has more or less this organization:

{
  "key1": {
    "key1.1": {
      "key": "valor",
      "key": "valor"
    },
    "key1.2": {}
  },
  "key2": {
    "key2.1": {
      "key": "valor",
      "key": "valor"
    },
    "key2.2": {}
  },
  "key3": {
    "key": "valor",
    "key": "valor",
    "key": "valor"
  }
}

In certain scenarios i only need to change one of the keys inside the key1.1, then I would only need update its value and keep the others, but what is done (even using that MERGE structure I put up there) is that it replaces all the contents of key1.1 by the new JSON passed, in this case deleting the other keys that were not mentioned in the UPDATE.

There just happens to be some effective way to merge directly into Postgresql, or will I really need to always create the object in the same structure and include the data I don’t want to change as well, to replace the entire JSON?

Answer 1

I ended up finding the solution to my problem.

To merge any JSON key that is after the 1st level o operador de concatenação '||' does not serve, it can merge only keys in the first level, after that it performs overlay.

To be able to merge a specific subkey independent of the level I used the function jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]).

The 4 parameters (in order) represent:

1. The JSON in which you will be making a change (name of my column in the bank)
2. The way up to the key in which I wish to make amendments in her internal JSON
3. The JSON to be inserted at the location specified in the second parameter
4. Boolean which indicates if you want to overwrite the change (false) or insert the change next to the one you already have (true).

Depending on the JSON architecture of the question, to merge the content within the key1.1, the structure will be as follows:

update tabela set colunaJSON = jsonb_set(colunaJSON,'{key1,key1.1}',colunaJSON->'key1'->'key1.1' || '{"chave1": "valor", "chave3": "valor}',false);

See that in the same way, I still had to use the operator || to merge the information. If the information is not used the information will be rewritten.