如果包含字符串的json文档,如何从MySQL(5.6)列获取值 [英] How to get values from MySQL(5.6) column if that contains json document as string
问题描述
如果MySQL(5.6)列包含JSON文档作为字符串,如何从MySQL(5.6)列获取值
How to get values from MySQL(5.6) column if that contains JSON document as a string
例如,如果我们有一个表-员工,那么我们有三列 ID,姓名和学历. 列教育包含数据作为JSON文档
For example, if we have a table - employee in that we have three columns id, name and educations. and column educations contains data as a JSON document
{"ug":"bsc","pg":"mca","ssc":"10th"}
我需要教育"列中的ug和pg值
I need the value of ug and pg from educations column
我们可以使用MySQL(5.6)查询吗?
Can we do that using MySQL(5.6) queries?
推荐答案
要能够做您想做的事,您需要MySQL 5.7.8+.从5.7.8版本开始,您可以使用JSON_EXTRACT
函数从JSON字符串中提取值:
To be able to do what you want to, you need MySQL 5.7.8+. Since 5.7.8 you can use JSON_EXTRACT
function to extract a value from a JSON string:
SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+
来自此处.
在MySQL 5.6中,您只是无法获得所需的值,因为MySQL对JSON对象是什么一无所知.因此,您的选择是:
In MySQL 5.6 you just can't get the value you want as MySQL doesn't know anything about what a JSON object is. So your options are:
- 升级到5.7.8 +
- 使用可处理JSON的内容解析查询结果:
- 可以是PHP json_decode (或您所用语言的等效语言)
- 一种在线工具,例如 http://json.parser.online.fr/
- Upgrade to 5.7.8+
- Parse the query result with something that handles JSON:
- Could be PHP json_decode (or equivalent in your language)
- An online tool like http://json.parser.online.fr/
这篇关于如果包含字符串的json文档,如何从MySQL(5.6)列获取值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!