JSON函数
<ul>
<li>MySql-JSON函数</li>
</ul>
<pre><code>测试:{"userId": "001", "displayName": "张三"}
JSON_EXTRACT("列名","$.key")
SELECT * from (select JSON_EXTRACT(remark, '$.displayName') as name from test_json) as b;
查询结果:
"张三"</code></pre>
<ul>
<li>案例</li>
</ul>
<pre><code>
#查询多个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;
</code></pre>