MySql Json函数

• mysql Json函数

测试:{"userId": "001", "displayName": "张三"}
JSON_EXTRACT("列名","$.key")
SELECT * from (select JSON_EXTRACT(remark, '$.displayName') as name from test_json) as b;
查询结果:
"张三"

• 案例

#查询多个key会吧结果以jsonArray的形式返回
SELECT JSON_EXTRACT(remark, "$.displayName","$.userId") from test_json;

#JSON_EXTRACT json提取函数
SELECT a.`name`,b.displayName from test_json as a LEFT JOIN test_j as b on JSON_EXTRACT(remark,"$.displayName")=b.displayName;


#高版本 支持 字段->'$.key'
SELECT remark from test_json where remark -> '$.displayName'="张三";

SELECT remark -> '$.displayName' from test_json;


#json_unquote 去掉双引号
SELECT JSON_UNQUOTE(JSON_EXTRACT(remark,"$.displayName")) from test_json;

#->> 也能去掉双引号
SELECT remark ->> '$.displayName' from test_json;