Cách lấy Mảng JSON từ SQL select

Question

Tôi muốn lấy một mảng các đối tượng bên trong lựa chọn SQL của mình.

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

và tôi nhận được phản hồi này:

[
  { 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' }
]

là nó có thể nhận được một cái gì đó như thế này?

[
  { 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

một chút gỉ, nhưng hãy thử điều này:

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;

CẬP NHẬT: ở trên không dành cho SQLite. Đối với SQLite, bạn nên kiểm tra phần mở rộng json1 tại đây:https://www.sqlite.org/json1.html

CẬP NHẬT KHÁC: nó có thể được thực hiện mà không cần mở rộng 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

có một vấn đề nhỏ ở đây: cột / nút màu được coi là một chuỗi chứ không phải một mảng ... điều này là bình thường vì sqlite không hỗ trợ một cột kiểu mảng. một số phân tích cú pháp sẽ cần được thực hiện để chuyển đổi từ chuỗi sang mảng ...

Answer 2

Sử dụng json_build_object và 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