SQL select에서 JSON 배열을 얻는 방법

Question

내 SQL 선택 내부에 개체 배열을 얻고 싶습니다.

SELECT id, a.name antibodyName, c.name colorName, c.location colorLocation 
FROM Antibodies a 
JOIN AssignedColors c ON id = antiId 
WHERE colorId IS NOT NULL

이 응답을 얻습니다.

[
  { id: 1, antibodyName: 'antibody 1', colorName: 'red', colorLocation: 'A5/C3' },
  { id: 2, antibodyName: 'antibody 2', colorName: 'red', colorLocation: 'C3/A1' },
  { id: 2, antibodyName: 'antibody 2', colorName: 'yellow', colorLocation: 'E4/F2' }
]

이와 같은 것을 얻을 수 있습니까?

[
  { id: 1, antibodyName: 'antibody 1', colors: [{name: 'red, location: 'A5/C3'}] },
  { id: 2, antibodyName: 'antibody 2', colors: [{name: 'red, location: 'C3/A1'}, {name: 'yellow', location: 'E4/F2'}] },
]

Answer 1

약간 녹슬었지만 이것을 시도하십시오.

SELECT
a.id AS 'Id',
a.Name AS 'antibodyName',
c.Name AS 'name',
c.location AS 'location'
FROM Antibodies a
LEFT JOIN AssignedColors c ON c.id = a.Id
FOR JSON AUTO;

업데이트 : 위의 내용은 SQLite가 아닙니다. SQLite의 경우 여기에서 json1 확장을 확인해야합니다.https://www.sqlite.org/json1.html

또 다른 업데이트 : JSON 확장없이 수행 할 수 있습니다 ...

SELECT
a.id AS 'id',
a.Name AS 'antibodyName',
(SELECT '[' || GROUP_CONCAT('{name:"' || Name || '", location:"' || location || '"}') ||']'
 FROM AssignedColors
 WHERE c.id=Id) AS colors
 FROM Antibodies a
 LEFT JOIN AssignedColors c ON c.id = a.id
 GROUP BY a.id

여기에 작은 문제가 있습니다. 색상 열 ​​/ 노드는 배열이 아닌 문자열로 처리됩니다. sqlite가 배열 유형의 열을 지원하지 않기 때문에 이것은 정상입니다. 문자열에서 배열로 변환하려면 일부 구문 분석을 수행해야합니다.

Answer 2

json_build_object 및 array_agg를 사용하십시오.

WITH foobar AS ( 
    SELECT id, a.name antibodyName, c.name colorName, c.location colorLocation 
    FROM Antibodies a JOIN AssignedColors c
    ON id = antiId WHERE colorId IS NOT NULL
    )
SELECT 
     json_build_object('data',array_agg(json_build_object('id', id, 'antibodyName', antibodyName, 'colorName', colorName, 'colorLocation', colorLocation))
FROM 
    foobar