如何从 SQL 选择中获取 JSON 数组 [英] How to get Array of JSON from SQL select
问题描述
我想在我的 SQL 选择中获取一组对象.
SELECT id, a.name 抗体名称, c.name colorName, c.location colorLocationFROM 抗体 aJOIN AssignedColors c ON id = antiId哪里 colorId 不为空
我得到了这个回复:
<预><代码>[{ id: 1, 抗体名称: '抗体 1', 颜色名称: '红色', 颜色位置: 'A5/C3' },{ id: 2, 抗体名称: '抗体 2', 颜色名称: '红色', 颜色位置: 'C3/A1' },{ id: 2, 抗体名称: '抗体 2', 颜色名称: '黄色', 颜色位置: 'E4/F2' }]有没有可能得到这样的东西?
<预><代码>[{ id: 1, 抗体名称: '抗体 1', 颜色: [{名称: '红色, 位置: 'A5/C3'}] },{ id:2,抗体名称:'抗体 2',颜色:[{名称:'红色,位置:'C3/A1'},{名称:'黄色',位置:'E4/F2'}]},]有点生疏,但试试这个:
SELECTa.id AS 'Id',a.Name AS 'antibodyName',c.Name AS 'name',c.location AS '位置'FROM 抗体 aLEFT JOIN AssignedColors c ON c.id = a.Id对于 JSON 自动;
更新:以上不适用于 SQLite.对于 SQLite,您应该在这里查看 json1 扩展:https://www.sqlite.org/json1.html
另一个更新:它可以在没有 JSON 扩展的情况下完成...
SELECTa.id AS 'id',a.Name AS 'antibodyName',(SELECT '[' || GROUP_CONCAT('{name:"' || Name || '", location:"' || location || '"}') ||']'来自指定的颜色WHERE c.id=Id) AS 颜色FROM 抗体 aLEFT JOIN AssignedColors c ON c.id = a.id按 a.id 分组
这里有一个小问题:colors 列/节点被视为字符串而不是数组......这是正常的,因为 sqlite 不支持数组类型的列.需要进行一些解析才能将字符串转换为数组...
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'}] },
]
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...
这篇关于如何从 SQL 选择中获取 JSON 数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!