MySQL 5.7+, значение JSON_SET во вложенном пути
Для недавнего проекта разработки мы используем MySQL 5.7, поэтому мы можем воспользоваться преимуществами новейших JSON-функций …
Я создаю UPDATE-запрос, где вложенный json-объект должен быть вставлен / добавлен в столбец атрибутов типа JSON, см. Запрос ниже.
UPDATE `table` SET `table`.`name` = 'Test',
`table`.`attributes` = JSON_SET(
`table`.`attributes`,
"$.test1", "Test 1",
"$.test2.test3", "Test 3")
Когда я выполняю этот запрос, поле атрибутов содержит данные
{"test1": "Test 1"}
вместо разыскиваемого
{"test1", "Test 1", "test2": {"test3", "Test 3"}}
Также пытался использовать JSON_MERGE, но когда я выполняю его несколько раз, он создает JSON-объект, такой как
{"test1": ["Test 1", "Test 1", "Test 1"... etc.], "test2": {"test3": ["Test 3", "Test 3", "Test 3"... etc.]}}
Итак, JSON_SET не работает, когда узлы не существуют? JSON_MERGE сливается до бесконечности?
Ключи, используемые в JSON-объекте, могут быть определены пользователем, поэтому невозможно создать пустой JSON-объект для всех возможных ключей. Действительно ли нам нужно выполнять запрос JSON_CONTAINS / JSON_CONTAINS_PATH перед каждым запросом UPDATE, чтобы определить, нужно ли нам использовать JSON_SET или JSON_MERGE / JSON_APPEND?
Мы ищем способ иметь запрос, который всегда работает, поэтому, когда "$.test4.test5.test6" дается, он будет расширять текущий JSON-объект, добавляя полный путь … Как это можно сделать?
Решение
Предполагая, что вы хотите конечный результат
{"test1": "Test 1", "test2": {"test3": "Test 3"}}
В вашем примере attributes столбец, который обновляется, имеет значение {"test1": "Test 1"}
Глядя на ваш начальный UPDATE запрос, мы можем увидеть $.test2.test3 не существует.
Так что его нельзя установить как
JSON_SET () Вставляет или обновляет данные в документе JSON и возвращает
результат. Возвращает NULL, если какой-либо аргумент равен NULL или путь, если дан,
не найти объект.
Значение MySQL может добавить $.test2, но с тех пор $.test2 не является объектом, MySQL не может добавить к $.test2.test3,
Так что вам нужно будет определить $.test2 как объект JSON, выполнив следующее.
mysql> SELECT * FROM testing;
+----+---------------------+
| id | attributes |
+----+---------------------+
| 1 | {"test1": "Test 1"} |
+----+---------------------+
1 row in set (0.00 sec)
mysql> UPDATE testing
-> SET attributes = JSON_SET(
-> attributes,
-> "$.test1", "Test 1",
-> "$.test2", JSON_OBJECT("test3", "Test 3")
-> );
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM testing;
+----+---------------------------------------------------+
| id | attributes |
+----+---------------------------------------------------+
| 1 | {"test1": "Test 1", "test2": {"test3": "Test 3"}} |
+----+---------------------------------------------------+
1 row in set (0.00 sec)
Поэтому вместо того, чтобы полагаться на точечную нотацию MySQL, вам нужно явно указать MySQL, что ключ существует как объект JSON.
Это похоже на то, как PHP также определяет несуществующие значения свойств объекта.
$a = (object) ['test1' => 'Test 1'];
$a->test2->test3 = 'Test 3';
//PHP Warning: Creating default object from empty value
Чтобы избавиться от ошибки, вам нужно сначала определить $a->test2 как объект.
$a = (object) ['test1' => 'Test 1'];
$a->test2 = (object) ['test3' => 'Test 3'];
В качестве альтернативы вы можете протестировать и создать объекты до использования точечной нотации, чтобы установить значения. Хотя с большими наборами данных это может быть нежелательно.
mysql> UPDATE testing
-> SET
-> attributes = JSON_SET(attributes, "$.test2", IFNULL(attributes->'$.test2',JSON_OBJECT())),
-> attributes = JSON_SET(attributes, "$.test4", IFNULL(attributes->'$.test4', JSON_OBJECT())),
-> attributes = JSON_SET(attributes, "$.test4.test5", IFNULL(attributes->'$.test4.test5', JSON_OBJECT())),
-> attributes = JSON_SET(
-> attributes,
-> "$.test2.test3", "Test 3"-> );
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM testing;
+----+---------------------------------------------------------------------------+
| id | attributes |
+----+---------------------------------------------------------------------------+
| 1 | {"test1": "Test 1", "test2": {"test3": "Test 3"}, "test4": {"test5": {}}} |
+----+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
Хотя в любом случае, если исходные данные не предоставлены, вызов функции JSON_OBJECT опустошит значение (я) свойств вложенного объекта. Но, как вы можете видеть из последнего JSON_SET запрос, $.test1 не был предоставлен в определении attributesи он остался без изменений, поэтому те свойства, которые не были изменены, могут быть опущены в запросе.
Другие решения
Fyrye, спасибо за удивление, очень ценю это! Поскольку данные не имеют фиксированной структуры и могут отличаться для каждой отдельной записи, мне нужно было решение, в котором я мог бы сгенерировать запрос, который бы автоматически генерировал полный JSON-объект в одном запросе.
Мне очень нравится ваше решение с использованием JSON_SET(attributes, "$.test2", IFNULL(attributes->'$.test2',JSON_OBJECT())) метод. Поскольку я продолжил поиск, я также сам нашел решение, используя JSON_MERGE функция.
Когда я выполняю обновление, я использую JSON_MERGE объединить пустой JSON-объект с полем в базе данных для всех ключей с подузлами, чтобы они были доступны в JSON-поле в базе данных и после этого, используя JSON_SET обновить значения. Итак, полный запрос выглядит так:
UPDATE table SET
-> attributes = JSON_MERGE(
-> attributes, '{"test2": {}, "test4": {"test5": {}}}'),
-> attributes = JSON_SET(attributes, "$.test2.test3", "Test 3");
После выполнения этого запроса результат будет выглядеть примерно так:
mysql> SELECT * FROM testing;
+----+---------------------------------------------------------------------------+
| id | attributes |
+----+---------------------------------------------------------------------------+
| 1 | {"test1": "Test 1", "test2": {"test3": "Test 3"}, "test4": {"test5": {}}} |
+----+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
Я не знаю, какой метод лучше сейчас, оба работают на данный момент. Будем делать некоторые тесты скорости в будущем, чтобы проверить, как они преформируются, когда 1 обновляет 10.000 строк!
После поиска везде, как и многие из вас, я нашел лучшее возможное решение, перечисленное здесь: https://forums.mysql.com/read.php?20,647956,647969#msg-647969
С сайта:
он узлов и подузлов, но не содержит никаких данных …
Таким образом, в приведенном выше примере объект будет выглядеть так:
{"nodes": {}}
При выполнении обновления я использую JSON_MERGE объединить пустой JSON-объект с полем в базе данных, чтобы все узлы / подузлы были доступны в JSON-поле в базе данных и после этого, используя JSON_SET обновить значения. Итак, полный запрос выглядит так:
UPDATE table SET attributes = JSON_MERGE(attributes, '{"nodes": {}'), attributes = JSON_SET(attributes, "$.nodes.node2", "Node 2")
Пока это работает.
Но это странный обходной путь. Может быть, это можно было рассмотреть в предыдущих версиях MySQL, поэтому JSON_SET также создает parent-узлы, когда установлены подузлы?