Как получить значения из столбца MySQL (5.6), если он содержит документ JSON в виде строки
Например, если у нас есть таблица - сотрудник, у нас есть три столбца id, имя и образование. и образование столбцов содержит данные как документ JSON
{"ug":"bsc","pg":"mca","ssc":"10th"}
Мне нужно значение ug и pg из колонки образования
Можем ли мы это сделать с помощью запросов MySQL (5.6)?
Чтобы иметь возможность делать то, что вы хотите, вам нужен 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" |
+---------------------------------------------------------+
Взято из здесь.
В MySQL 5.6 вы просто не можете получить требуемое значение, поскольку MySQL ничего не знает о том, что такое объект JSON. Итак, ваши варианты:
В MySQL 5.6 по умолчанию JSON_EXTRACT недоступно по умолчанию.
Если вам все еще нужно получить доступ к данным json в MySQL 5.6, вам нужно написать пользовательскую функцию.
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 ;
Это поможет. Я создал его и протестировал.
Оба предыдущих ответа не работали для меня, когда элемент не упоминался в тексте 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 ;
Вот мои 3 хранимых функции SQL, которые я использую для извлечения JSON. Они обрабатывают вложенные объекты, но заботятся только об имени ключа. Ключи должны быть строками, значения могут быть строковыми, числовыми или логическими значениями. Массивы плохо обрабатываются, выбирается только первое значение. Они возвращают NULL если значение не найдено.
Первый, json_extract_1 выбирает только первое значение, если существует несколько ключей с одинаковым именем. Если вы выходите из предложения LIMIT 1, он выдает "Подзапрос возвращает более 1 строки", если найдено больше ключей (безопасный режим).
Второй, json_extract_m собирает все значения с одним и тем же ключом в списке через запятую.
Третий, json_extract_c, самый медленный, но он также корректно обрабатывает значения с запятыми. Используйте его, если это абсолютно необходимо, например, текстовые описания.
Для всех трех ограничение составляет 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 ;
Да, и если у вас есть возможность, попробуйте обновить до MySQL 5.7, встроенные функции работают намного эффективнее.
Ответ Рахула не сработал для меня, поэтому я отредактировал его, и это сработало для меня:
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 ;
Ниже ответ работает для меня. удаляет двойные кавычки из значения.
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 ;
Функция выше не работает должным образом, если вы вложили JSON в поле таблицы.
Поскольку мне нужно было JSON_EXTRACT в MySQL 5.6, я написал сам копию оригинальной функции, которая может извлекать значения, такие как встроенная функция в MySQL 5.7.
Использование:
SELECT JSON_EXTRACT_NESTED(table_field,"json_level1.json_level2.json_level3") FROM table;
Если у вас есть один уровень JSON, то вы используете:
SELECT JSON_EXTRACT_NESTED(table_field,"json_level1") FROM table;
В базу данных вы должны добавить две функции:
Основная функция:
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 ;
Вспомогательная функция (необходима основной функции):
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 ;
Предлагая этот альтернативный взгляд на ответы, данные здесь, для тех из вас (как я), которые могут не видеть интуитивно понятные манипуляции со строками в функциях 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 ;