MySQL의 JSON 어레이를 행으로 변환
업데이트: 이것은 MySQL 8에서 JSON_TABLE 기능을 통해 가능합니다.https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html
MySQL 5.7의 새로운 JSON 함수는 마음에 들지만, JSON의 값을 일반 테이블 구조로 병합하려는 블록에 부딪힙니다.
JSON의 취득, 어레이의 조작, 추출 등은 간단합니다.JSON_EXTRACT 입니다.그러나 JSON 배열에서 행으로 이동하는 역방향은 어떨까요?기존 MySQL JSON 기능에 대해 자세히 알고 있는 것 같습니다만, 그것을 이해할 수 없었습니다.
예를 들어, JSON 어레이가 있는데 어레이의 각 요소에 대해 해당 값을 사용하여 행을 삽입하려고 합니다.제가 찾은 유일한 방법은 JSON_EXTRACT(...) 한 묶음을 쓰는 것입니다.'$[0]' JSON_EXTRACT(... '$[1]' 등)와 결합합니다.
또는 JSON 배열을 가지고 있는데 그것을 콤마로 구분된 단일 문자열로 GROUP_CONCAT()하고 싶다고 합시다.
즉, 할 수 있다는 것을 알고 있습니다.
SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
FROM
(
SELECT 0 AS n
UNION
SELECT 1 AS n
UNION
SELECT 2 AS n
UNION
SELECT 3 AS n
UNION
SELECT 4 AS n
UNION
SELECT 5 AS n
) x
WHERE x.n < JSON_LENGTH(@j);
하지만 그건 내 눈을 아프게 해.그리고 내 마음.
어떻게 하면 좋을까요?
SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))
어레이 내의 값과 JSON 어레이 자체의 값을 연결합니까?
여기서 찾고 있는 것은 다음과 같은 JSON_SPLIT입니다.
SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(val)
FROM
JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')
MySQL에 적절한 STRING_SPLIT(val, separator) 테이블 반환 함수가 있으면 해킹할 수 있지만(도피할 수 없습니다).
JSON으로 정규화 해제하는 것은 좋지 않지만 JSON 데이터를 처리해야 할 경우가 있으며 쿼리 내 행으로 JSON 어레이를 추출하는 방법도 있습니다.
이 트릭은 인덱스의 임시 테이블 또는 인라인테이블에서 조인을 실행하는 것입니다.이 테이블에서는 JSON 배열의 null이 아닌 각 값에 대해 행을 지정합니다.즉, 값이 0, 1, 2인 테이블이 2개의 엔트리가 있는 JSON 배열 "fish"에 결합되어 있는 경우 fish[0]는 0과 일치하여 행이1이 되고 fish1은 두 번째 행이 되지만 fish[2]는 null이므로 결합에서 행이 생성되지 않습니다.인덱스 테이블에는 JSON 데이터의 배열 최대 길이만큼 많은 숫자가 필요합니다.이건 약간 해킹이고 OP의 예처럼 고통스럽지만 매우 편리합니다.
예(MySQL 5.7.8 이후 필요):
CREATE TABLE t1 (rec_num INT, jdoc JSON);
INSERT INTO t1 VALUES
(1, '{"fish": ["red", "blue"]}'),
(2, '{"fish": ["one", "two", "three"]}');
SELECT
rec_num,
idx,
JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishes
FROM t1
-- Inline table of sequential values to index into JSON array
JOIN (
SELECT 0 AS idx UNION
SELECT 1 AS idx UNION
SELECT 2 AS idx UNION
-- ... continue as needed to max length of JSON array
SELECT 3
) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;
결과는 다음과 같습니다.
+---------+-----+---------+
| rec_num | idx | fishes |
+---------+-----+---------+
| 1 | 0 | "red" |
| 1 | 1 | "blue" |
| 2 | 0 | "one" |
| 2 | 1 | "two" |
| 2 | 2 | "three" |
+---------+-----+---------+
MySQL 팀은 이 모든 것을 쉽게 하기 위해 MySQL 8에 기능을 추가할 수 있을 것 같습니다. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/) (MySQL 팀은 기능을 추가했습니다.)
MySQL 8+에서 JSON_TABLE을 사용하는 방법은 다음과 같습니다.
SELECT *
FROM
JSON_TABLE(
'[5, 6, 7]',
"$[*]"
COLUMNS(
Value INT PATH "$"
)
) data;
구분된 문자열을 JSON 문자열로 변환하여 MySQL에 없는 일반적인 문자열 분할 함수(PG의 regexp_split_to_table 또는 MS SQL의 STRING_SPLIT와 유사함수)로도 사용할 수 있습니다.
set @delimited = 'a,b,c';
SELECT *
FROM
JSON_TABLE(
CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
"$[*]"
COLUMNS(
Value varchar(50) PATH "$"
)
) data;
2018년.이 사건에서 내가 뭘 할 건지
숫자만 연속해서 늘어선 테이블을 준비하세요.
CREATE TABLE `t_list_row` ( `_row` int(10) unsigned NOT NULL, PRIMARY KEY (`_row`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough;JSON 어레이를 향후 행까지 간단하게 즐길 수중에 넣을 수 있습니다.
SET @j = '[1, 2, 3]'; SELECT JSON_EXTRACT(@j, CONCAT('$[', B._row, ']')) FROM (SELECT @j AS B) AS A INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);
이 방법은 Chris Hynes와 같은 방식이지만 배열 크기를 알 필요는 없습니다.
Good: clear, short, easy code, 어레이 크기 확인 불필요, 루프 없음, 호출 기타 기능은 고속입니다.
불량: 충분한 행이 있는 테이블이 하나 더 필요합니다.
간단한 예:
select subtotal, sku
from t1,
json_table(t1.refund_line_items,
'$[*]' columns (
subtotal double path '$.subtotal',
sku char(50) path '$.line_item.sku'
)
) refunds
JSON_TABLE 함수를 사용할 수 없지만 재귀 CTE를 사용할 수 있는 경우 다음을 수행할 수 있습니다.
SET @j = '[1, 2, 3]';
WITH RECURSIVE x AS (
/* Anchor, start at -1 in case empty array */
SELECT -1 AS n
UNION
/* Append indexes up to the length of the array */
SELECT x.n + 1
FROM x
WHERE x.n < JSON_LENGTH(@j) - 1
)
/* Use the table of indexes to extract each item and do your GROUP_CONCAT */
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']')))
FROM x
/* This prevents selecting from empty array */
WHERE x.n >= 0
그러면 각 배열 항목에 대한 순차 인덱스 테이블이 생성됩니다. 이 테이블을 사용하여 JSON_EXTRACT를 사용하여 값을 가져올 수 있습니다.
MySQL 8+의 경우 다음 답변을 참조하십시오.
이전 버전의 경우 다음과 같이 수행합니다.
- 새 테이블 만들기
pseudo_rows값이 0 ~ 99인 경우 키로서 사용됩니다(배열 값이 100을 초과하는 경우 값을 추가합니다).pseudo_rows).
메모: MariaDB 를 실행하고 있는 경우는, 이것을 생략하고, 의사 시퀀스 테이블을 사용할 수 있습니다(예:seq_0_to_99).
CREATE TABLE `pseudo_rows` (
`row` int(10) unsigned NOT NULL,
PRIMARY KEY (`row`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT pseudo_rows VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92), (93), (94), (95), (96), (97), (98), (99)
- 이 예에서는 테이블을 사용합니다.
events아티스트 그룹을 저장합니다.
CREATE TABLE `events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`artists` json DEFAULT NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `events` (`id`, `artists`) VALUES ('1', '[{\"id\": 123, \"name\": \"Pink Floyd\"}]');
INSERT INTO `events` (`id`, `artists`) VALUES ('2', '[{\"id\": 456, \"name\": \"Nirvana\"}, {\"id\": 789, \"name\": \"Eminem\"}]');
모든 아티스트를 한 줄에 하나씩 가져오기 위한 쿼리는 다음과 같습니다.
SELECT
JSON_UNQUOTE(JSON_EXTRACT(events.artists, CONCAT('$[', pseudo_rows.row, '].name'))) AS performer
FROM events
JOIN pseudo_rows
HAVING performer IS NOT NULL
그리고 결과 집합은 다음과 같습니다.
performer
---------
Pink Floyd
Nirvana
Eminem
내 경우,JSON기능을 사용할 수 없어서 해킹을 했습니다.Chris MYSQL에서 언급한 바와 같이,STRING_SPLIT하지만 그것은 가지고 있다substring_index.
입력의 경우
{
"requestId":"BARBH17319901529",
"van":"0xxxxx91317508",
"source":"AxxxS",
"txnTime":"15-11-2017 14:08:22"
}
다음을 사용할 수 있습니다.
trim(
replace(
substring_index(
substring(input,
locate('requestid',input)
+ length('requestid')
+ 2), ',', 1), '"', '')
) as Requestid`
출력은 다음과 같습니다.
BARBH17319901529
필요에 따라 수정할 수 있습니다.
한 열에 큰 json 배열 목록이 있는 보고서를 작성하고 있었습니다.데이터모델을 수정하여 모든 것을 한 열에 저장하는 것이 아니라 1부터 *까지의 관계를 저장했습니다.이 프로세스를 수행하려면 최대 크기를 모르기 때문에 저장 프로시저에서 잠시 동안 사용해야 했습니다.
DROP PROCEDURE IF EXISTS `test`;
DELIMITER #
CREATE PROCEDURE `test`()
PROC_MAIN:BEGIN
DECLARE numNotes int;
DECLARE c int;
DECLARE pos varchar(10);
SET c = 0;
SET numNotes = (SELECT
ROUND (
(
LENGTH(debtor_master_notes)
- LENGTH( REPLACE ( debtor_master_notes, "Id", "") )
) / LENGTH("Id")
) AS countt FROM debtor_master
order by countt desc Limit 1);
DROP TEMPORARY TABLE IF EXISTS debtorTable;
CREATE TEMPORARY TABLE debtorTable(debtor_master_id int(11), json longtext, note int);
WHILE(c <numNotes) DO
SET pos = CONCAT('$[', c, ']');
INSERT INTO debtorTable(debtor_master_id, json, note)
SELECT debtor_master_id, JSON_EXTRACT(debtor_master_notes, pos), c+1
FROM debtor_master
WHERE debtor_master_notes IS NOT NULL AND debtor_master_notes like '%[%' AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL AND JSON_EXTRACT(debtor_master_notes, pos) IS NOT NULL;
SET c = c + 1;
END WHILE;
SELECT * FROM debtorTable;
END proc_main #
DELIMITER ;
언급URL : https://stackoverflow.com/questions/39906435/convert-json-array-in-mysql-to-rows
'programing' 카테고리의 다른 글
| MySQL 5.7을 새로운 MySQL 8.0으로 업데이트하려면 어떻게 해야 합니까? (0) | 2022.10.01 |
|---|---|
| HTTPS/SSL을 통한 Java 클라이언트 인증서 (0) | 2022.10.01 |
| Join을 사용한 JPA 업데이트 (0) | 2022.10.01 |
| 함수에서의 디폴트 인수 사용 (0) | 2022.10.01 |
| Larabel - 임시 삭제 - 투고를 다른 테이블로 이동 (0) | 2022.10.01 |