需求描述:

　　通過mysql中的json_merge函數(shù),可以將多個json對象合并成一個對象

操作過程:

1.查看一張包含json列的表

mysql> select * from tab_json; +----+-----------------------------------------------------------------------------------+ | id | data | +----+-----------------------------------------------------------------------------------+ | 1 | {"Tel": "132223232444", "name": "david", "address": "Beijing"} | | 2 | {"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"} | | 3 | {"names": "Smith"} | | 4 | {"names": "Smith", "address": "Beijing"} | | 5 | {"names": "Smith", "address": "Beijing", "birthday": "2018-09-09"} | | 6 | {"Max": "true", "names": "Smith", "address": "Beijing", "birthday": "2018-09-09"} | | 7 | {"max": "true", "names": "Smith", "address": "Beijing", "birthday": "2018-09-09"} | | 8 | {"oax": "true", "names": "Smith", "address": "Beijing", "birthday": "2018-09-09"} | +----+-----------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)

2.將names的值與address的值進行合并

mysql> select json_extract(data,'$.names'),json_extract(data,'$.address') from tab_json; +------------------------------+--------------------------------+ | json_extract(data,'$.names') | json_extract(data,'$.address') | +------------------------------+--------------------------------+ | NULL | "Beijing" | | NULL | "Guangzhou" | | "Smith" | NULL | | "Smith" | "Beijing" | | "Smith" | "Beijing" | | "Smith" | "Beijing" | | "Smith" | "Beijing" | | "Smith" | "Beijing" | +------------------------------+--------------------------------+ 8 rows in set (0.00 sec)mysql> select json_merge(json_extract(data,'$.names'),json_extract(data,'$.address')) from tab_json; +-------------------------------------------------------------------------+ | json_merge(json_extract(data,'$.names'),json_extract(data,'$.address')) | +-------------------------------------------------------------------------+ | NULL | | NULL | | NULL | | ["Smith", "Beijing"] | | ["Smith", "Beijing"] | | ["Smith", "Beijing"] | | ["Smith", "Beijing"] | | ["Smith", "Beijing"] | +-------------------------------------------------------------------------+ 8 rows in set (0.00 sec)

3.如果多個對象含有相同的key,那么也會進行合并為具體的values

mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}'); +----------------------------------------------------+ | JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') | +----------------------------------------------------+ | {"a": [1, 4], "b": 2, "c": 3} | +----------------------------------------------------+ 1 row in set (0.00 sec)

備注:將兩個對象的值合并成一個,a這個key的值也增加到了2個.

?

文檔創(chuàng)建:2018年6月6日17:49:18

轉(zhuǎn)載于:https://www.cnblogs.com/chuanzhang053/p/9146466.html

總結(jié)

以上是生活随笔為你收集整理的mysql中json_merge函数的使用?的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。