제목을 다음과 같이 다시 작성하십시오. JSON 배열을 MySQL의 행으로 변환

Question

업데이트: 이는 이제 JSON_TABLE 함수를 통해 MySQL 8에서 가능합니다: 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
  에서
  (
    0을 n으로 선택
    노동 조합
    1을 n으로 선택
    노동 조합
    2를 n으로 선택
    노동 조합
    3을 n으로 선택
    노동 조합
    4를 n으로 선택
    노동 조합
    5를 n으로 선택
  )엑스
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(값)
에서
  JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')

MySQL에 적절한 STRING_SPLIT(val, 'separator') 테이블 반환 함수가 있다면 해킹할 수 있지만(망할 탈출) 그것도 작동하지 않습니다.

Answer 1

JSON으로 비정규화하는 것이 좋은 생각이 아닌 것은 사실이지만 JSON 데이터를 처리해야 할 때도 있고, 쿼리에서 JSON 배열을 행으로 추출하는 방법도 있습니다.

비결은 임시 또는 인라인 인덱스 테이블에서 조인을 수행하여 JSON 배열의 null이 아닌 각 값에 대한 행을 제공하는 것입니다. 즉, 값이 0, 1, 2인 테이블이 있고 이를 두 개의 항목이 포함된 JSON 배열 "fish"에 조인하면 Fish[0]은 0과 일치하여 행을 생성하고 Fish 1은 1과 일치하고 두 번째 행이 생성되지만 fish[2]는 null이므로 2와 일치하지 않으며 조인에서 행이 생성되지 않습니다. JSON 데이터에 있는 배열의 최대 길이만큼 인덱스 테이블에 많은 숫자가 필요합니다. 이것은 약간의 해킹이고 OP의 예만큼 고통스럽지만 매우 편리합니다.

예(MySQL 5.7.8 이상 필요):

으아악

결과는 다음과 같습니다.

으아악

MySQL 팀은 이 작업을 더 쉽게 하기 위해 MySQL 8에 JSON_TABLE 함수를 추가할 수도 있습니다. (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/JSON_TABLE 函数，以使这一切变得更容易。 (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/) （MySQL 团队已经添加了JSON_TABLE) (MySQL 팀은 JSON_TABLE 함수.)

Answer 2

MySQL 8+에서 JSON_TABLE을 사용하는 방법은 다음과 같습니다.

으아악

구분된 문자열을 JSON 문자열로 변환하여 MySQL에 없는 범용 문자열 분할 함수(PG의 regexp_split_to_table 또는 MSSQL의 STRING_SPLIT와 유사)로 사용할 수도 있습니다.

으아악