반응형
MySQL(5.6) 열에 json 문서가 문자열로 포함된 경우 값을 가져오는 방법
MySQL(5.6) 열에 JSON 문서가 문자열로 포함된 경우 값을 가져오는 방법
예를 들어, 테이블이 있다면 직원이라는 세 개의 열 ID, 이름, 교육입니다.및 컬럼 교육에는 데이터가 JSON 문서로 포함됩니다.
{"ug":"bsc","pg":"mca","ssc":"10th"}
교육란의 ug와 pg 값이 필요합니다.
MySQL(5.6) 쿼리를 사용하여 실행할 수 있습니까?
수5.7.85.7.은 MySQL 5.7.8+를 사용할 수 .JSON_EXTRACTJSON:
SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan" |
+---------------------------------------------------------+
여기서부터 가져갑니다.
MySQL 5.6에서는 MySQL이 JSON 개체에 대해 아무것도 모르기 때문에 원하는 값을 얻을 수 없습니다.옵션은 다음과 같습니다.
- 5.7.8 이상으로 업그레이드
- JSON을 사용하다
- PHP json_decode(또는 동등한 언어)일 수 있습니다.
- http://json.parser.online.fr/ 등의 온라인 툴
으로 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 ;
이게 도움이 될 거야.나는 그것을 만들고 테스트했다.
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 ;
JSON을 사용하다이들은 중첩된 개체를 처리하지만 키 이름에만 관심이 있습니다.키는 문자열이어야 하며 값은 문자열, 숫자 또는 부울 값이어야 합니다.어레이는 적절하게 처리되지 않고 첫 번째 값만 선택됩니다.NULL값을 찾을 수 없는 경우.
번째, 첫번 the the 。json_extract_1는 같은 이름의 키가 여러 개 있는 경우 첫 번째 값만 선택합니다.「 」를 는,LIMIT 1더 많은 키가 발견되면 '서브쿼리는 1개 이상의 행을 반환합니다'(안전 모드).
번째, ㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇㅇ.json_extract_m는 같은 키를 사용하여 모든 값을 쉼표로 구분된 목록으로 수집합니다.
one, the the the the the the 。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로 업그레이드해 보세요.내장 기능이 훨씬 더 효율적으로 작동합니다.
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 ;
테이블 필드에 중첩된 JSON이 있는 경우 위의 기능이 제대로 작동하지 않습니다.
mysql 5.6에 JSON_EXTRACT가 필요했기 때문에 mysql 5.7의 네이티브 함수처럼 값을 추출할 수 있는 원래 함수 복사본을 직접 작성했습니다.
사용방법:
SELECT JSON_EXTRACT_NESTED(table_field,"json_level1.json_level2.json_level3") FROM table;
1레벨의 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 ;
가 전개한 이은,, 이전 this this this this this this this this이다.json_extract_c5.6MySQL 5.6MySQL 서 in
테스트 완료:
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
지원되지 않음:
- 중첩된 json
- 값의 json 값)
select json_extract_c('{"a\"a" : "9", "a" : "a\"a"}', "$.a");-> a
제한:
- 개 첫 됩니다.
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 ;
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 ;
아래 답변이 좋습니다. 큰따옴표를 값에서 제거합니다.
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 ;
SUBSTRING_INDEX하다
select * from (select id, name,
substring_index(substring_index(educations, "ug\":\"", -1), "\"", 1) as ug,
substring_index (substring_index(educations, "pg\":\"", -1), "\"", 1) as pg
from employee) as t1;
효과가 있으면 알려주세요.위의 열을 기준으로 조건을 추가할 수도 있습니다.
파티에 늦었다.
현재 우리는 json이 mysql과 mariaDB의 새로운 버전을 지원한다는 것을 알고 있습니다.(mysql 5.7+와 maria에서)DB 10.2+)
그러나 이전 mysql 버전 5.6을 사용하고 있는 곳도 있습니다.예: 일부 공유 호스팅 프로바이더.
인 '아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 네.JSON_EXTRACT().
사용 사례:
select * From offer;
+----------+-------------------------------+-----------------------+
| offer_id | terms | name |
+----------+-------------------------------+-----------------------+
| 1 | {"qty": 3, "total": 600.00} | x |
| 2 | {"qty": 2, "discount": 15.00} | Buy 2 GET ONE 15% OFF |
| 3 | {"discount": 9.09} | Buy 1 SAVE 10/- |
+----------+-------------------------------+-----------------------+
4 rows in set (0.001 sec)
SELECT offer_id
, terms
, json_extract_c(terms, 'qty') as offer_qty
, json_extract_c(terms, 'discount') as offer_discount
, json_extract_c(terms, 'total') as offer_total
FROM offer;
+----------+-------------------------------+-----------+----------------+-------------+
| offer_id | terms | offer_qty | offer_discount | offer_total |
+----------+-------------------------------+-----------+----------------+-------------+
| 1 | {"qty": 3, "total": 600.00} | 3 | NULL | 600.00 |
| 2 | {"qty": 2, "discount": 15.00} | 2 | 15.00 | NULL |
| 3 | {"discount": 9.09} | NULL | 9.09 | NULL |
+----------+-------------------------------+-----------+----------------+-------------+
4 rows in set (0.003 sec)
커스텀 기능:
DELIMITER $$
CREATE FUNCTION `json_extract_c`(
target VARCHAR(50)
, jkey VARCHAR(50)
) RETURNS varchar(10) CHARSET utf8
BEGIN
DECLARE newtarget VARCHAR(50) DEFAULT trim(leading '{' from trim(trailing '}' from target));
DECLARE targa VARCHAR(50) DEFAULT '';
DECLARE thekey VARCHAR(50);
DECLARE theval VARCHAR(10);
WHILE LENGTH(newtarget) > 0 DO
SET targa = substring_index(newtarget, ',', 1);
IF LOCATE(',', newtarget) > 0 THEN
SET newtarget = substring_index(newtarget, ',', -1);
ELSE
SET newtarget = '';
END IF;
SET thekey = substring_index(targa, ':', 1);
SET thekey = TRIM(BOTH '"' FROM TRIM(thekey));
SET theval = substring_index(targa, ':', -1);
IF thekey = jkey THEN
RETURN TRIM(theval);
END IF;
END WHILE;
RETURN NULL;
END$$
json 문서를 문자열로 저장하면 다음 함수를 통해 json 필드의 모든 값을 가져올 수 있습니다.
JSON_EXTRACT(JSON_UNQUOTE(<field-name>), "$.*")
JSON_UNQUOTE는 문자열에서 json을 해석한 후 지정된 경로에 따라 값을 추출합니다."$*"는 json 객체의 모든 키를 나타냅니다.
MySQL JSON 공통 스키마
https://code.google.com/archive/p/common-schema/httpscode.google.com/archive/p//
https://github.com/shlomi-noach/common_schemahttpsgithub.com/shlomi-noach/
네, mysql의 JSON_EXTract() 함수를 사용하여 확실하게 실행할 수 있습니다.
JSON JSON(표 JSON)을 .client_services여기) :
+-----+-----------+--------------------------------------+
| id | client_id | service_values |
+-----+-----------+------------+-------------------------+
| 100 | 1000 | { "quota": 1,"data_transfer":160000} |
| 101 | 1000 | { "quota": 2,"data_transfer":800000} |
| 102 | 1000 | { "quota": 3,"data_transfer":70000} |
| 103 | 1001 | { "quota": 1,"data_transfer":97000} |
| 104 | 1001 | { "quota": 2,"data_transfer":1760} |
| 105 | 1002 | { "quota": 2,"data_transfer":1060} |
+-----+-----------+--------------------------------------+
각 JSON 필드를 선택하려면 다음 쿼리를 수행합니다.
SELECT
id, client_id,
json_extract(service_values, '$.quota') AS quota,
json_extract(service_values, '$.data_transfer') AS data_transfer
FROM client_services;
따라서 출력은 다음과 같습니다.
+-----+-----------+----------------------+
| id | client_id | quota | data_transfer|
+-----+-----------+----------------------+
| 100 | 1000 | 1 | 160000 |
| 101 | 1000 | 2 | 800000 |
| 102 | 1000 | 3 | 70000 |
| 103 | 1001 | 1 | 97000 |
| 104 | 1001 | 2 | 1760 |
| 105 | 1002 | 2 | 1060 |
+-----+-----------+----------------------+
이것으로 문제가 해결되길 바랍니다!
언급URL : https://stackoverflow.com/questions/37816269/how-to-get-values-from-mysql5-6-column-if-that-contains-json-document-as-strin
반응형
'programing' 카테고리의 다른 글
| 약속 체인에서의 set Timeout 사용 (0) | 2023.02.26 |
|---|---|
| ASP.NET MVC 3 (레이저) 에이잭스Action Link - 내가 뭘 잘못하고 있지? (0) | 2023.02.26 |
| react use객체 비교 효과 (0) | 2023.02.26 |
| 인터페이스 정의에서 getters/setters를 사용할 수 있습니까? (0) | 2023.02.26 |
| AngularJs에서 정수를 문자열로 변환하려면 어떻게 해야 합니까? (0) | 2023.02.26 |