解析字符串
-- A23=02001&A5=591&A33=CTRad
str_to_map(字段名,'&','=')['参数名'] as x
解析json
get_json_object(string json_string, string path)
-- 说明:
-- 第一个参数填写json对象变量,第二个参数使用$表示json变量标识,然后用 . 或 [] 读取对象或数组
-- 如果输入的json字符串无效,那么返回NULL
-- 每次只能返回一个数据项
data =
{
"store":
{
"fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"xx@xx.com",
"age":18
}
get单层值
select get_json_object(data, '$.email') from 表名;
-- xx@xx.com
get多层值
select get_json_object(data, '$.store.bicycle.price') from 表名;
-- 19.95
get数组值
select get_json_object(data, '$.store.fruit[0]') from 表名;
-- {"weight":8,"type":"apple"}