MySQL查询将键:值附加到JSON字符串 [英] MySQL query to append key:value to JSON string

问题描述

我的表有一列带有嵌套对象的 JSON 字符串(因此简单的 REPLACE 函数无法解决此问题).例如像这样:{'name':'bob', 'blob': {'foo':'bar'}, 'age': 12}.将值附加到 JSON 字符串末尾的最简单查询是什么?例如，我希望最终结果如下所示: {'name':'bob', 'blob': {'foo':'bar'}, 'age': 12, 'gender': 'male'} 该解决方案应该足够通用以适用于任何 JSON 值.

My table has a column with a JSON string that has nested objects (so a simple REPLACE function cannot solve this problem) . For example like this: {'name':'bob', 'blob': {'foo':'bar'}, 'age': 12}. What is the easiest query to append a value to the end of the JSON string? So for the example, I want the end result to look like this: {'name':'bob', 'blob': {'foo':'bar'}, 'age': 12, 'gender': 'male'} The solution should be generic enough to work for any JSON values.

推荐答案

这个怎么样

UPDATE table SET table_field1 = CONCAT(table_field1,' This will be added.');

我个人会在插入之前使用 PHP 之类的语言进行操作.容易多了.无论如何，这就是你想要的吗?如果您添加的 json 格式为 {'key':'value'}

I personally would have done the manipulation with a language like PHP before inserting it. Much easier. Anyway, Ok is this what you want? This should work providing your json format that is being added is in the format {'key':'value'}

 UPDATE table
 SET col = CONCAT_WS(",", SUBSTRING(col, 1, CHAR_LENGTH(col) - 1),SUBSTRING('newjson', 2));

这篇关于MySQL查询将键:值附加到JSON字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！