วิธีรับ Array of JSON จาก SQL select

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