1. 关于JSON_EXTRACT函数的使用,先贴一段代码解释:
SELECT DISTINCT json_extract(`Jdoc`, '$."Customer City Name"')
FROM `gc_master_data_0`
WHERE `Fiscal Year` = 2016
LIMIT 10
注释:Jdoc是字段名,"Customer City Name"是Jdoc字段中json文件中的key。Query结果如下:
2. 可以对提取的字段重新命名,代码为:
SELECT DISTINCT json_extract(`Jdoc`, '$."Customer City Name"') AS `Customer City Name`
FROM `gc_master_data_0`
WHERE `Fiscal Year` = 2016
LIMIT 10
3. 当然这里有一个符号(->)可以用来替换函数功能,代码为:
SELECT DISTINCT `Jdoc`->'$."Customer City Name"' AS `Customer City Name`
FROM `gc_master_data_0`
WHERE `Fiscal Year` = 2016
LIMIT 10
实际上,
`Jdoc`->'$."Customer City Name"'
就是相当于表的一个字段,只不过这个字段是从Jdoc字段的json文件中提取。
4. 如果想获取value的大小,可以使用符号(->>),贴代码:
SELECT DISTINCT `Jdoc`->>'$."Customer City Name"' AS `Customer City Name`
FROM `gc_master_data_0`
WHERE `Fiscal Year` = 2016
LIMIT 10
Reference:
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html