The best way to parse array of objects Postgresql?

Question

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?

Answer 1

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));