MySQL 5.7+，嵌套路径中的JSON_SET值

php json mysql mysql-json

2022-08-30 22:00:44

对于最近的开发项目，我们使用的是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 对象。我们真的需要在每次UPDATE查询之前执行JSON_CONTAINS/JSON_CONTAINS_PATH查询，以确定我们是需要使用JSON_SET还是JSON_MERGE/JSON_APPEND？

我们正在寻找一种方法来使查询始终有效，因此当给定时，它将扩展当前的JSON对象，添加完整路径...如何做到这一点？"$.test4.test5.test6"

答案 1

从MySQL版本5.7.13开始，假设您希望最终结果

{"test1": "Test 1", "test2": {"test3": "Test 3"}}

在您的示例中，正在更新的列设置为attributes{"test1": "Test 1"}

查看您的初始查询，我们可以看到不存在。所以不能设为UPDATE$.test2.test3

JSON_SET（）在 JSON 文档中插入或更新数据并返回结果。如果任何参数为 NULL 或 path（如果给定）未找到对象，则返回 NULL。

这意味着MySQL可以添加，但由于不是对象，MySQL不能添加到.$.test2$.test2$.test2.test3

因此，您需要通过执行以下操作来定义为 json 对象。$.test2

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密钥作为JSON对象存在，而不是依赖MySQL点表示法。

这类似于 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$.test1attributes

答案 2

现在，从MySQL版本5.7.22开始，最简单的方法是像这样使用：JSON_MERGE_PATCH

UPDATE `table` SET `attributes` = 
JSON_MERGE_PATCH(`attributes`, '{"test2": {"test3": "Test 3"}, "test4": {"test5": {}}}')

这给出了预期结果，如您的示例所示。{"test1": "Test 1", "test2": {"test3": "Test 3"}, "test4": {"test5": {}}}