mysql 修改JSON类型中某个字段的值

mysql  修改JSON类型中某个字段的值

mysql  修改JSON类型中某个JSON字段的值

例：修改Facebook 里面 email 的值

{
    "Facebook": {
        "identifier": "xxxxxxxxxxx",
        "profileURL": "none",
        "webSiteURL": "none",
        "photoURL": "none",
        "displayName": "test2 test2",
        "description": "none",
        "firstName": "test2",
        "lastName": "test2",
        "gender": 2,
        "language": "none",
        "age": "none",
        "birthDay": "none",
        "birthMonth": "none",
        "birthYear": "none",
        "email": "test@test.com",
        "emailVerified": "none",
        "Added": null,
        "phone": "none",
        "address": "none",
        "country": "none",
        "region": "none",
        "city": "none",
        "zip": "none"
    },
    "Google": {
        "identifier": "xxxxxxxxxxxxxxxxxxxxxx",
        "profileURL": "none",
        "webSiteURL": "none",
        "photoURL": "none",
        "displayName": "test2 test2",
        "description": "none",
        "firstName": "test2",
        "lastName": "test2",
        "gender": 2,
        "language": "none",
        "age": "none",
        "birthDay": "none",
        "birthMonth": "none",
        "birthYear": "none",
        "email": "test@test.com",
        "emailVerified": "none",
        "Added": null,
        "phone": "none",
        "address": "none",
        "country": "none",
        "region": "none",
        "city": "none",
        "zip": "none"
    }
}

sql ：

UPDATE 表名称 SET 字段名称= json_set(字段名称,'$.Facebook.email',“需要替换的内容”) where 条件

如果是json 里面套json  $后面跟最后一层JSON对应的名称就行，会自动去匹配的

如果修改报错，提示   Data truncation: Invalid JSON text in argument 1 to function json_set: "Invalid encoding in string." at position 161.

是因为json字段的内容不符合json格式，将其修改后重新修改即可。

SELECT id FROM ( SELECT id, CASE WHEN JSON_VALID(json) THEN JSON_EXTRACT(json, "$.dictParameters") ELSE NULL END AS result FROM monitor_work_crawl_task  WHERE  crawl_type =0 )a WHERE a.result IS NULL;