如果包含 json 文档作为字符串，如何从 MySQL(5.6) 列中获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37816269/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get values from MySQL(5.6) column if that contains json document as string
提问by Poppy
How to get values from MySQL(5.6) column if that contains JSON document as a string
如果包含 JSON 文档作为字符串，如何从 MySQL(5.6) 列中获取值
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
例如，如果我们有一个表-employee，其中我们有三列id、name 和educations。和列教育包含数据作为 JSON 文档
{"ug":"bsc","pg":"mca","ssc":"10th"}
I need the value of ug and pg from educations column
我需要来自教育专栏的 ug 和 pg 的值
Can we do that using MySQL(5.6) queries?
我们可以使用 MySQL(5.6) 查询来做到这一点吗？
回答by Alvaro Fla?o Larrondo
To be able to do what you want to, you need MySQL 5.7.8+. Since 5.7.8 you can use JSON_EXTRACTfunction to extract a value from a JSON string:
为了能够做你想做的事，你需要 MySQL 5.7.8+。从 5.7.8 开始，您可以使用JSON_EXTRACT函数从 JSON 字符串中提取值：
SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+
Taken from here.
取自这里。
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:
在 MySQL 5.6 中，您无法获得所需的值，因为 MySQL 对 JSON 对象是什么一无所知。所以你的选择是：
- 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/
- 升级到 5.7.8+
- 使用处理 JSON 的内容解析查询结果：
- 可能是 PHP json_decode（或您的语言中的等价物）
- 像http://json.parser.online.fr/这样的在线工具
回答by Rahul
In MySQL 5.6, by default JSON_EXTRACTis not available by default.
If you still need to access json data in MySQL 5.6, you need to write custom function.
在 MySQL 5.6 中，默认情况下默认JSON_EXTRACT不可用。
如果在 MySQL 5.6 中仍然需要访问 json 数据，则需要编写自定义函数。
DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE DEFINER=`root`@`%` FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
RETURN TRIM(
BOTH '"' FROM SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
details,
CONCAT(
'"',
SUBSTRING_INDEX(required_field,'$.', - 1),
'"'
),
- 1
),
'",',
1
),
':',
- 1
)
) ;
END$$
DELIMITER ;
This will help. I have created it and tested.
这会有所帮助。我已经创建并测试了它。
回答by Janis Baiza
Both previous answers didn't work for me when the element wasn't mentioned in JSON text. There is my improved function:
当 JSON 文本中未提及该元素时，之前的两个答案对我都不起作用。有我改进的功能：
DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
DECLARE search_term TEXT;
SET details = SUBSTRING_INDEX(details, "{", -1);
SET details = SUBSTRING_INDEX(details, "}", 1);
SET search_term = CONCAT('"', SUBSTRING_INDEX(required_field,'$.', - 1), '"');
IF INSTR(details, search_term) > 0 THEN
RETURN TRIM(
BOTH '"' FROM SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
details,
search_term,
- 1
),
',"',
1
),
':',
-1
)
);
ELSE
RETURN NULL;
END IF;
END$$
DELIMITER ;
回答by gaborsch
Here are my 3 SQL stored functions that I use for JSON extracting. They handle nested objects, but only care about the key name. The keys must be strings, the values can be strings, numeric or boolean values. Arrays are not treated well, only the first value is picked. They return NULLif no value found.
这是我用于 JSON 提取的 3 个 SQL 存储函数。它们处理嵌套对象，但只关心键名。键必须是字符串，值可以是字符串、数字或布尔值。数组没有得到很好的处理，只选择第一个值。NULL如果没有找到值，它们将返回。
The first one, json_extract_1picks only the first value if there are multiple keys with the same name. If you leave the LIMIT 1clause, it throws 'Subquery returns more than 1 row' if more keys found (safe mode).
json_extract_1如果有多个具有相同名称的键，则第一个只选择第一个值。如果您离开该LIMIT 1子句，如果找到更多键（安全模式），它会抛出“子查询返回多于 1 行”。
The second, json_extract_mcollects all values with the same key in a comma-separated list.
第二，json_extract_m在逗号分隔的列表中收集具有相同键的所有值。
The third one, json_extract_cis the slowest one, but it treats values with commas also correctly. Use it if it's absolutely necessary, e.g textual descriptions.
第三个，json_extract_c是最慢的，但它也正确地处理带逗号的值。如果绝对必要，请使用它，例如文本描述。
For all three the limitation is 999 keys. You can speed up if you prepare a table for the numberssubselect.
对于所有三个，限制是 999 个键。如果为子numbers选择准备表，则可以加快速度。
DELIMITER $$
/*
* Single-value JSON extract - picks the first value
*/
DROP FUNCTION IF EXISTS `json_extract_1`$$
CREATE FUNCTION `json_extract_1`(json_txt TEXT, search_key VARCHAR (255))
RETURNS TEXT
BEGIN
RETURN (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(txt,':',-1), '"', 2), '"', -1)) AS val
FROM (
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( SUBSTRING_INDEX(json_txt , ',', n), ',', -1 ), '}', 1), '{', -1)) AS txt
FROM (SELECT t1.v + t2.v*10 + t3.v*100 AS n
FROM (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3) numbers
WHERE CHAR_LENGTH(json_txt ) - CHAR_LENGTH(REPLACE(json_txt , ',', '')) >= n - 1
AND n>0 ) sp
WHERE TRIM(SUBSTRING_INDEX(txt,':',1)) = CONCAT('"',search_key,'"')
LIMIT 1 -- comment out for safe mode
);
END$$
/*
* Multi-value JSON extract - collects all values, group_concats them with comma
*/
DROP FUNCTION IF EXISTS `json_extract_m`$$
CREATE FUNCTION `json_extract_m`(json_txt TEXT, search_key VARCHAR (255))
RETURNS TEXT
BEGIN
RETURN (SELECT GROUP_CONCAT(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(txt,':',-1), '"', 2), '"', -1))) AS val
FROM (
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( SUBSTRING_INDEX(json_txt , ',', n), ',', -1 ), '}', 1), '{', -1)) AS txt
FROM (SELECT t1.v + t2.v*10 + t3.v*100 AS n
FROM (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3) numbers
WHERE CHAR_LENGTH(json_txt ) - CHAR_LENGTH(REPLACE(json_txt , ',', '')) >= n - 1
AND n>0 ) sp
WHERE TRIM(SUBSTRING_INDEX(txt,':',1)) = CONCAT('"',search_key,'"'));
END$$
/*
* Comma-safe JSON extract - treats values with commas correctly, but slow like hell
*/
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(json_txt TEXT, search_key VARCHAR (255))
RETURNS TEXT
BEGIN
DROP TEMPORARY TABLE IF EXISTS json_parts;
DROP TEMPORARY TABLE IF EXISTS json_parts2;
DROP TEMPORARY TABLE IF EXISTS json_indexes;
CREATE TEMPORARY TABLE json_parts AS
SELECT n, IF(INSTR(txt,':')>0 AND (INSTR(txt,',')+INSTR(txt,'{')>0),1,0) AS this_val, IF(INSTR(txt,':')>0 AND (INSTR(txt,',')+INSTR(txt,'{')=0),1,0) AS next_val, IF(INSTR(txt,',')+INSTR(txt,'{')>0,1,0) AS next_key, txt
FROM (SELECT n, IF(n%2,txt,REPLACE(txt,',','|')) AS txt
FROM (SELECT n, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(json_txt , '"', n), '"', -1 )) AS txt
FROM (SELECT t1.v + t2.v*10 + t3.v*100 AS n
FROM (SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
(SELECT 0 AS v UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3) numbers
WHERE CHAR_LENGTH(json_txt ) - CHAR_LENGTH(REPLACE(json_txt , '"', '')) >= n - 1
AND n>0) v
) v2;
CREATE TEMPORARY TABLE json_parts2 AS SELECT * FROM json_parts;
CREATE TEMPORARY TABLE json_indexes AS
SELECT p1.n, p1.n+1 AS key_idx, MIN(GREATEST(IF(p2.this_val,p2.n,0), IF(p2.next_val,p2.n+1,0))) AS val_idx, p2.this_val AS trim_val
FROM json_parts p1
JOIN json_parts2 p2 ON (p1.n < p2.n AND (p2.this_val OR p2.next_val))
WHERE p1.next_key
GROUP BY p1.n;
RETURN (SELECT json_values.v
FROM (SELECT p1.txt AS k, REPLACE(IF(i.trim_val, regexp_replace(regexp_replace(p2.txt,'^[: {]+',''),'[, }]+$',''), p2.txt), '|', ',') AS v
FROM json_indexes i
JOIN json_parts p1 ON (i.key_idx = p1.n)
JOIN json_parts2 p2 ON (i.val_idx = p2.n)) json_values
WHERE json_values.k = search_key);
END$$
DELIMITER ;
Yep, and if you have the chance, try to upgrade to MySQL 5.7, the built-in functions work much more efficiently.
是的，如果有机会，请尝试升级到 MySQL 5.7，内置函数的工作效率更高。
回答by user3631341
Rahul's answer did not work quite well for me, so I edited it and this worked for me:
Rahul 的回答对我来说效果不佳，所以我对其进行了编辑，这对我有用：
DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
SET details = TRIM(LEADING '{' FROM TRIM(details));
SET details = TRIM(TRAILING '}' FROM TRIM(details));
RETURN TRIM(
BOTH '"' FROM SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
details,
CONCAT(
'"',
SUBSTRING_INDEX(required_field,'$.', - 1),
'":'
),
- 1
),
',"',
1
),
':',
-1
)
) ;
END$$
DELIMITER ;
回答by Rok Furlan
Function above not work properly if you have nested JSON in table field.
如果您在表字段中嵌套了 JSON，则上述功能无法正常工作。
Because i needed a JSON_EXTRACT on mysql 5.6 i wrote it by myself a copy of original function that can extract values like the native function in mysql 5.7
因为我需要在 mysql 5.6 上使用 JSON_EXTRACT 我自己写了一份原始函数的副本，该函数可以像 mysql 5.7 中的本机函数一样提取值
Usage:
用法：
SELECT JSON_EXTRACT_NESTED(table_field,"json_level1.json_level2.json_level3") FROM table;
If you have one level JSON then you use:
如果您有一层 JSON，那么您可以使用：
SELECT JSON_EXTRACT_NESTED(table_field,"json_level1") FROM table;
In database you have to add two functions:
在数据库中，您必须添加两个函数：
Main function:
主功能：
CREATE FUNCTION `json_extract_nested`(
_field TEXT,
_variable TEXT
) RETURNS TEXT CHARSET latin1
BEGIN
DECLARE X INT DEFAULT 0;
DECLARE fieldval1 TEXT;
DECLARE arrayName,arrayValue TEXT;
SET arrayName = SUBSTRING_INDEX(_variable, '.', 1);
IF(LOCATE('%',arrayName)> 0) THEN
SET _field = SUBSTRING_INDEX(_field, "{", -1);
SET _field = SUBSTRING_INDEX(_field, "}", 1);
RETURN TRIM(
BOTH '"' FROM SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
_field,
CONCAT(
'"',
SUBSTRING_INDEX(_variable,'$.', - 1),
'":'
),
- 1
),
',"',
1
),
':',
-1
)
) ;
ELSE
SET arrayValue = json_array_value(_field, arrayName);
WHILE X < (LENGTH(_variable) - LENGTH(REPLACE(_variable, '.', ""))) DO
IF(LENGTH(_variable) - LENGTH(REPLACE(_variable, '.', ""))>X) THEN
SET arrayName = SUBSTRING_INDEX(SUBSTRING_INDEX(_variable, '.', X+2),'.',-1);
END IF;
IF(arrayName<>'') THEN
SET arrayValue = json_array_value(arrayValue, arrayName);
END IF;
SET X = X + 1;
END WHILE;
END IF;
RETURN arrayValue;
END$$
DELIMITER ;
Auxiliary function (needed by main function):
辅助功能（主功能需要）：
CREATE FUNCTION `json_array_value`(
_field TEXT,
arrayName VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
DECLARE arrayValue, arrayValueTillDelimit TEXT;
DECLARE arrayStartDelimiter, arrayEndDelimiter VARCHAR(10);
DECLARE arrayCountDelimiter INT;
DECLARE countBracketLeft, countBracketRight INT DEFAULT 0;
DECLARE X INT DEFAULT 0;
DECLARE arrayNameQuoted VARCHAR(255);
SET arrayNameQuoted = CONCAT('"',arrayName,'"');
/*check arrayname exist*/
IF(LOCATE(arrayNameQuoted,_field)= 0) THEN
RETURN NULL;
ELSE
/*get value behind arrayName1*/
SET _field = SUBSTRING(_field,1,LENGTH(_field)-1);
SET arrayValue = SUBSTRING(_field, LOCATE(arrayNameQuoted,_field)+LENGTH(arrayNameQuoted)+1, LENGTH(_field));
/*get json delimiter*/
SET arrayStartDelimiter = LEFT(arrayValue, 1);
IF(arrayStartDelimiter='{') THEN
SET arrayEndDelimiter = '}';
loopBrackets: WHILE X < (LENGTH(arrayValue)) DO
SET countBracketLeft = countBracketLeft +IF(SUBSTRING(arrayValue,X,1)=arrayStartDelimiter,1,0);
SET countBracketRight = countBracketRight +IF(SUBSTRING(arrayValue,X,1)=arrayEndDelimiter,1,0);
IF(countBracketLeft<>0 AND countBracketLeft=countBracketRight) THEN
SET arrayCountDelimiter = X;
LEAVE loopBrackets;
ELSE
SET X = X + 1;
END IF;
END WHILE;
ELSEIF(arrayStartDelimiter='[') THEN
SET arrayEndDelimiter = ']';
SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
ELSEIF(arrayStartDelimiter='"') THEN
SET arrayEndDelimiter = '"';
SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
ELSE
SET arrayStartDelimiter = "";
IF((LOCATE(",",arrayValue)> LOCATE("}",arrayValue))) THEN
SET arrayEndDelimiter = ",";
ELSE
SET arrayEndDelimiter = "}";
END IF;
SET arrayCountDelimiter = LENGTH(SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, 0));
END IF;
SET arrayValueTillDelimit = SUBSTRING(arrayValue, 1, arrayCountDelimiter);
SET arrayCountDelimiter = LENGTH(arrayValueTillDelimit) - LENGTH(REPLACE(arrayValueTillDelimit, arrayStartDelimiter, ""));
SET arrayValue = SUBSTR(arrayValue,LENGTH(arrayStartDelimiter)+1);
IF(arrayStartDelimiter='{') THEN
SET arrayValue = SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, arrayCountDelimiter);
ELSE
SET arrayValue = SUBSTRING_INDEX(arrayValue, arrayEndDelimiter, arrayCountDelimiter+1);
END IF;
RETURN (arrayValue);
END IF;
END$$
DELIMITER ;
回答by Gnought
This is our deployed json_extract_cin MySQL 5.6
这是我们json_extract_c在 MySQL 5.6 中部署的
Tested:
测试：
select json_extract_c('{"a": 1, "b": 2}', "$.a"); -> 1;
select json_extract_c('{"a": "1", "b": "2"}', "$.a"); -> 1;
select json_extract_c('{"a":"1","b":"2"}', "$.a"); -> 1;
select json_extract_c('{"a" :"1", "b" :"2"}', "$.a"); -> 1;
select json_extract_c('{"b" :"a", "a" :"2"}', "$.a"); -> 2;
select json_extract_c('{"a" : "a", "a" :"2"}', "$.a"); -> a;
select json_extract_c('{"a": "1"}', "$.a"); -> 1
select json_extract_c('{"a": "a"}', "$.a"); -> a
select json_extract_c('{"a" : "a"}', "$.a"); -> a
select json_extract_c('{"a.a" : "a"}', "$.a"); -> NULL
select json_extract_c('{"a\"a" : "9"}', "$.a"); -> NULL
Not supported:
不支持：
- Nested json
- Escaped double quotes in json values, e.g.
select json_extract_c('{"a\"a" : "9", "a" : "a\"a"}', "$.a");-> a
- 嵌套的json
- 在 json 值中转义双引号，例如
select json_extract_c('{"a\"a" : "9", "a" : "a\"a"}', "$.a");-> a
Limitation:
局限性：
- Only first field will be extracted if there are multiple same fields,
e.g. select json_extract_c('{"a" : 1, "a" : "2}', "$.a");-> 1
- 如果有多个相同的字段，只会提取第一个字段，
e.g. select json_extract_c('{"a" : 1, "a" : "2}', "$.a");-> 1
DELIMITER $$
DROP function if exists json_extract_c$$
CREATE FUNCTION json_extract_c(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET utf8mb4
DETERMINISTIC
NO SQL
BEGIN
DECLARE search_term, val TEXT;
DECLARE pos INT signed DEFAULT 1;
-- Remove '{' and '}'
SET details = SUBSTRING_INDEX(details, "{", -1);
SET details = SUBSTRING_INDEX(details, "}", 1);
-- Transform '$.xx' to be '"xx"'
SET search_term = CONCAT('"', SUBSTRING_INDEX(required_field,'$.', - 1), '"');
searching: LOOP
SET pos = LOCATE(search_term, details);
-- Keep searching if the field contains escape chars
WHILE pos > 0 AND RIGHT(LEFT(details, pos-1), 1) = '\'
DO
SET details = SUBSTR(details, pos+LENGTH(search_term));
SET pos = LOCATE(search_term, details);
END WHILE;
-- Return NULL if not found
IF pos <= 0 THEN
RETURN NULL;
END IF;
SET pos = LENGTH(search_term)+pos;
SET details = SUBSTR(details, pos);
SET val = TRIM(details);
-- see if we reach the value that is a leading colon ':'
IF LEFT(val, 1) = ':' THEN
RETURN TRIM(
TRAILING ',' FROM
TRIM(
SUBSTRING_INDEX(
TRIM(
BOTH '"' FROM TRIM(
SUBSTR(
val
, 2
)
)
)
, '"', 1
)
)
);
ELSE
ITERATE searching;
END IF;
END LOOP;
END$$
DELIMITER ;
回答by danbsd
Offering this alternative view of the answers given here for those of you (like me) who may not intuitively see the string manipulation within the SQL functions. This version will allow you to explicitly see each step of the text parsing. This works for MySQL 5.6 and can of course be combined back together and not use any variables.
为那些可能无法直观地看到 SQL 函数中的字符串操作的人（如我）提供这里给出的答案的另一种观点。此版本将允许您明确查看文本解析的每个步骤。这适用于 MySQL 5.6，当然可以重新组合在一起而不使用任何变量。
DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE FUNCTION `json_extract_c`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
/* get key from function passed required field value */
set @JSON_key = SUBSTRING_INDEX(required_field,'$.', -1);
/* get everything to the right of the 'key = <required_field>' */
set @JSON_entry = SUBSTRING_INDEX(details,CONCAT('"', @JSON_key, '"'), -1 );
/* get everything to the left of the trailing comma */
set @JSON_entry_no_trailing_comma = SUBSTRING_INDEX(@JSON_entry, ",", 1);
/* get everything to the right of the leading colon after trimming trailing and leading whitespace */
set @JSON_entry_no_leading_colon = TRIM(LEADING ':' FROM TRIM(@JSON_entry_no_trailing_comma));
/* trim off the leading and trailing double quotes after trimming trailing and leading whitespace*/
set @JSON_extracted_entry = TRIM(BOTH '"' FROM TRIM(@JSON_entry_no_leading_colon));
RETURN @JSON_extracted_entry;
END$$
DELIMITER ;
回答by Sapna Mishra
Below answer works for me. it remove double quotes from the value.
下面的答案对我有用。它从值中删除双引号。
DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_values`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `json_extract_values`(
details TEXT,
required_field VARCHAR (255)
) RETURNS TEXT CHARSET latin1
BEGIN
SET details = SUBSTRING_INDEX(details, "{", -1);
SET details = SUBSTRING_INDEX(details, "}", 1);
RETURN
SUBSTRING_INDEX(
TRIM(
TRAILING '"' FROM
SUBSTRING_INDEX(
details,
CONCAT(
'"',
SUBSTRING_INDEX(required_field,'$.', - 1),
'":'
),
-1 )
),
'"',
-1);
END$$
DELIMITER ;
