Update MySQL JSON column filtering in recursive estructure

Viewed 49

today I started using MySQL JSON fields and I'm a bit lost

I have the following table:

CREATE TABLE `formulario` (
  `id` int NOT NULL AUTO_INCREMENT,
  `values` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The JSON "values" has a recursive structure, so that a "section" can have "sections" inside:

[
  {
    "id": 1,
    "sections": [
      {
        "idSection": 1,
        "name": "S1",
        "sections": [
          {
            "idSection": 2,
            "name": "S2",
            "sections": [...]
          }
        ]
      }
    ]
  }
]

Let's suppose that I want to update the "name" attribute of the "section" with id = 2. What I want is to do an "UPDATE" query that somehow updates the "name" value of a specific JSON object, and I don't know how to do a "WHERE" or if there is any way to filter the JSON to get it.

I have tried to use the "JSON_SET" function, but I can't get it to work, considering that I don't know how deep the JSON can be

Extra info: I am using hibernate, but I also don't know if HQL gives you any way to achieve this, or can I build some "NativeQuery" dynamically where I can insert to the "JSON_SET" function the path of what I want to change by building it at runtime

0 Answers
Related