mysql 数据类型json的使用

取出json数据中的某个值

字段名:path,字段类型:json(储存数据为多个经纬度)

[[47.323231356034455, 123.65844025261708], [47.3276263714247, 123.66906200133384], [47.32571459960614, 123.67106197988323], [47.322963351397014, 123.66827417972333], [47.321674853727046, 123.66936799713244], [47.31963040655516, 123.66780309057162], [47.32207054221155, 123.66204886100265], [47.321662371523225, 123.65908583989608]]

需求1:取出pathz中第一个数据的经纬度:

 SELECT JSON_EXTRACT(path, '$[0][0]' ),JSON_EXTRACT(path, '$[0][1]')  FROM address;

结果为:

字段名:data_info
字段类型:json

{"q1": 3, "q2": "无霜期测试", "q3": "44", "q4": "23", "m1q3": "2", "m1q5": "55", "m1q7": "66", "m1q10": "1", "sfList": [{"m4q1": "2022-04-29", "m4q2": "1", "m4q4": "23", "m4q5": "好", "m4q6": "改进", "manure": 2}, {"m4q1": "2022-04-28", "m4q2": "3", "m4q4": "44", "m4q5": "一般", "m4q6": "改进2", "manure": 2}], "zgList": [{"m5q1": "", "m5q2": "", "m5q3": "", "m5q4": "", "m5q5": "", "m5q6": "", "m5q7": "", "m5q8": "", "m5q9": "", "m5q10": "", "m5q11": "", "m5q12": "", "m5q13": ""}]}

需求2:取出data_info中的q2,q3,q4的值

    SELECT	JSON_UNQUOTE (data_info->'$.q2')   as 'frost',JSON_UNQUOTE (data_info->'$.q3')   as rain_fall,JSON_UNQUOTE (data_info->'$.q4')   as acc_warm
        from info

需求3:查出data_info数据中,manure 为2 的数量,manure在sfList数组中

	    SELECT	count(1)
        from info
        where JSON_UNQUOTE(data_info->'$.sfList[*].manure') is not null  and del_flag='0'
        and JSON_CONTAINS(data_info->'$.sfList[*].manure',JSON_Array(2))

参考link:https://www.cnblogs.com/waterystone/p/5626098.html