JSON_OBJECT 및 JSON_ARRAY를 사용하여 MySQL 관계형 데이터에서 중첩된 JSON 개체를 생성하는 방법은 무엇입니까?

MySQL 네이티브 JSON 함수를 사용하여 중첩 JSON 개체 생성

MySQL 버전 5.7.12에는 네이티브 JSON 함수가 도입되어 관계형 데이터에서 직접 JSON 문서를 생성할 수 있습니다. 이를 달성하려면 JSON_OBJECT() 및 JSON_ARRAY() 함수를 활용할 수 있습니다.

제공된 예제 테이블을 고려하세요.

CREATE TABLE `parent_table` (
   `id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
);
CREATE TABLE `child_table` (
   `id` int(11) NOT NULL,
   `parent_id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`,`parent_id`)
);

목표는 중첩된 개체가 있는 JSON 문서를 생성하는 것입니다. 다음과 유사합니다:

[{
    "id" : 1,
    "desc" : "parent row 1",
    "child_objects" : [{
            "id" : 1,
            "parent_id" : 1,
            "desc" : "child row 1"
        }, {
            "id" : 2,
            "parent_id" : 1,
            "desc" : "child row 2"
        }
    ]
}]

오류

JSON 개체에 간단한 키-값 쌍이 입력으로 필요하기 때문에 이전 시도에서 "하위 쿼리가 1개 이상의 행을 반환합니다."라는 오류가 발생했습니다. 여러 행을 반환하는 하위 쿼리는 단일 문자열이나 JSON 배열로 변환해야 합니다.

해결 방법

이 오류를 극복하려면 MySQL의 GROUP_CONCAT() 함수를 사용할 수 있습니다. 다음 쿼리는 이를 수행합니다.

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',json_array(
                     (select GROUP_CONCAT(
                                 json_object('id',id,'parent_id',parent_id,'desc',`desc`)
                             )   
                      from child_table 
                      where parent_id = p.id))
                   )
 from parent_table p;

출력에서 이스케이프 문자를 제거하기 위해 쿼리는 CAST() 및 CONCAT() 함수를 사용하도록 수정됩니다.

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',(select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(
                    'id',id,'parent_id',parent_id,'desc',`desc`)),
                ']')
         AS JSON) from child_table where parent_id = p.id)

 ) from parent_table p;

이 세련된 쿼리 원하는 JSON 출력을 생성합니다.

'{\"id\": 1, 
\"desc\": \"parent row 1\", 
\"child_objects\": 
    [{\"id\": 1, 
    \"desc\": \"child row 1\", 
    \"parent_id\": 1
    }, 
    {\"id\": 2, 
    \"desc\": \"child row 2\", 
    \"parent_id\": 1
    }]  
}'

위 내용은 JSON_OBJECT 및 JSON_ARRAY를 사용하여 MySQL 관계형 데이터에서 중첩된 JSON 개체를 생성하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!