How to get Array of JSON from SQL select

Question

I want to get an array of objects inside of my SQL select.

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

and I get this response:

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

is it possible to get something like this?

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

Muhammad Ahmod · Accepted Answer · 2020-09-01 11:28:54Z

1

a bit rusty, but try this:

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;

UPDATE: the above is not for SQLite. For SQLite you should checkout the json1 extension here: https://www.sqlite.org/json1.html

ANOTHER UPDATE: it can be done without the 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

theres a small issue here: the colors column/node is treated as a string and not an array... this is normal as sqlite doesnt support a column of type array. some parsing will need to be done to convert from string to array...

edited Sep 1, 2020 at 11:28

answered Aug 31, 2020 at 11:26

Muhammad Ahmod

1894 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mj Ebrahimzadeh Over a year ago

are you this works in sqlite? because it gives me undefiend

Muhammad Ahmod Over a year ago

Hi @mj checkout the update to the answer... You right, it’s not going to work on sqlite

Muhammad Ahmod Over a year ago

I read the question and somehow skipped reading over the tags...

sheharbano · Accepted Answer · 2020-08-31 11:14:53Z

1

Use json_build_object and 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

answered Aug 31, 2020 at 11:14

sheharbano

2193 silver badges14 bronze badges

2 Comments

Mj Ebrahimzadeh Over a year ago

are you this works in sqlite? because it gives me undefiend

sheharbano Over a year ago

No this would work for postgresql. But you can modify it using this documentation. sqlite.org/json1.html

Collectives™ on Stack Overflow

How to get Array of JSON from SQL select

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related