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