SQL से JSON का Array कैसे प्राप्त करें

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 extention के बिना किया जा सकता है ...

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 doesnt प्रकार के कॉलम के स्तंभ का समर्थन नहीं करता है। स्ट्रिंग से एरे में बदलने के लिए कुछ पार्सिंग की आवश्यकता होगी ...

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