解析对象数组的最佳方法是Postgresql？
解析对象数组的最佳方法是Postgresql？
提问于 2022-05-19 12:21:26
我得到了结果：
tools: '{"(2,adobe-xd,\\"Adobe XD\\")","(42,wordpress,Wordpress)"}',
images: '{"(18,,)", "(19,heel,\\"\\")"}'来自typeorm查询函数：
select
...
coalesce(array_agg(distinct row(tools.id, tools.value, tools.name)) filter (where tools.id is not null), '{}') as tools,
coalesce(array_agg(distinct row(images.id, images.title, images.description)) filter (where images.id is not null), '{}') as images
from project
LEFT JOIN "project_tools_tool" "project_tools" ON "project_tools"."projectId"="project"."id"
LEFT JOIN "tool" "tools" ON "tools"."id"="project_tools"."toolId"
LEFT JOIN "project_image" "images" ON "images"."project_id"="project"."id"
where project.id in (select project.id from project order by project.order desc offset 3 limit 4)
group by project.id
order by project.order desc我希望将tools和images解析为普通的对象数组，例如：
tools: [
{
id: 2,
value: 'adobe-xd',
name: 'Adobe XD'
}, {
id: 42,
value: 'wordpress',
name: 'Wordpress'
}
],
images: [
{
id: 18,
title: null,
description: null
}, {
id: 19,
title: 'heel',
description: ''
}
]解析它的最佳方法是什么？可以在postgresql查询中解析它吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-05-19 12:41:28
这里有一个使用regex的解决方案，可能有现有的库可以这样做，但是如果您想要DIY的话：
const tools = '{"(2,adobe-xd,\\"Adobe XD\\")","(42,wordpress,Wordpress)"}';
const images = '{"(18,,)", "(19,heel,\\"\\")"}';
const parsePostgresqlObject = str => {
return JSON.parse(str.replace(/^{/, '[').replace(/}$/, ']')).map(chunk => {
const [_, ...matches] = chunk.match(/^\((.*?),(.*?),(.*)\)$/);
const [id, title, description] = matches.map(e => {
if(!e) return null;
try {
return JSON.parse(e);
} catch(ex) {
return e;
}
});
return { id, title, description };
});
};
console.log(parsePostgresqlObject(tools));
console.log(parsePostgresqlObject(images));
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72304440
复制相关文章
相似问题
腾讯云开发者
