I'm getting the result:
tools: '{"(2,adobe-xd,\\"Adobe XD\\")","(42,wordpress,Wordpress)"}',
images: '{"(18,,)", "(19,heel,\\"\\")"}'
from typeorm query function:
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
And I want to parse tools and images into normal object array like:
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: ''
}
]
What is the best way to parse it? Is it possible to parse it in postgresql query?
Here's a solution using regex, probably there are existing libraries to do it but if you want to 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));