Comment obtenir un tableau de JSON à partir de SQL Select

Question

Je veux obtenir un tableau d'objets à l'intérieur de ma sélection 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

et j'obtiens cette réponse:

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

est-il possible d'obtenir quelque chose comme ça?

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

un peu rouillé, mais essayez ceci:

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;

MISE À JOUR: ce qui précède n'est pas pour SQLite. Pour SQLite, vous devriez vérifier l'extension json1 ici:https://www.sqlite.org/json1.html

UNE AUTRE MISE À JOUR: cela peut être fait sans l'extension 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

il y a un petit problème ici: la colonne / nœud de couleurs est traitée comme une chaîne et non comme un tableau ... c'est normal car sqlite ne prend pas en charge une colonne de type array. des analyses devront être effectuées pour convertir une chaîne en tableau ...

Answer 2

Utilisez json_build_object et 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