How to remove json field in mysql

Mysql tips for retrieving json fields

Sometimes some information is stored in the database in json form. If it is too long, sql will be used during the retrieval process. The operation will be slower, and it will be more wasteful if you only get certain key values.

Use the function JSON_EXTRACT() in mysql

±—±---------------------------------------------------------------+
| id | data |
±—±---------------------------------------------------------------+
| 1 | {“Tel”: “132223232444”, “name”: “david”, “address”: “Beijing”} |
| 2 | {“Tel”: “13390989765”, “name”: “Mike”, “address”: “Guangzhou”} |
±—±---------------------------------------------------------------+

select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel");

Use get_json_object() in spark sql

select get_json_object(i.extra, '$.sale_currency');
select sum(get_json_object(i.extra, '$.sale_price') * i.count) as sale_price;

Mysql takes json. If the key is not known, get its value details

{ "selects" :{ "20071090" :{ "NN" : 41,
"LXFS1" : "12365",
"GH" : "20071090",
"RZZW" : "办公室主任",
"sxzym" : "园林植物与观赏园艺",
"ZC" : "副教授",
"XGW" : "行政管理后勤",
"XB" : "男",
"ZZMM" : "中共党员",
"ZWZC" : "高级职称",
"MZ" : "汉",
"CSRQ" : 307123200000,
"XL" : "硕士",
"selectKey" : "20071090",
"XM" : "张三",
"GZBM" : "办公室",
"PZGW" : "副教授三级岗位",
"XW" : "农学硕士" }}}

If you want to get a name in this mysql database, the content is as shown in the picture above

Create a view and take the name of this field. Since its key: "20071090" is dynamic and uncertain, and will only appear once in this place, first I take the first layer key==>selects The value

SELECT ZDLSXM->'$.selects' from VI_YXGR

On this basis, take down another layer. Since this layer is a dynamic key, the first step ZDLSXM->'$ cannot be used. selects' method, so I chose the substring_index method to remove the braces, and then used CONCAT() to splice it into a new json. Delete the red area of ​​the icon below

##

 SELECT CONCAT("{",substring_index(substring_index(ZDLSXM->'$.selects', '{', -1),"}",1),"}") from VI_YXGR

You can use this name by wrapping it one more layer

SELECT SE_ZDLSXM->'$.name' AS ZDLSXM from (
SELECT CONCAT("{",substring_index(substring_index(ZDLSXM->'$.selects', '{', -1),"}",1),"}") as SE_ZDLSXM  from VI_YXGR
) sss

The above is the detailed content of How to remove json field in mysql. For more information, please follow other related articles on the PHP Chinese website!