Estoy obteniendo el resultado:
tools: '{"(2,adobe-xd,\\"Adobe XD\\")","(42,wordpress,Wordpress)"}', images: '{"(18,,)", "(19,heel,\\"\\")"}'de la función de consulta 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 Y quiero analizar tools e images en una matriz de objetos normal como:
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: '' } ]¿Cuál es la mejor manera de analizarlo? ¿Es posible analizarlo en la consulta postgresql?
Aquí hay una solución que usa expresiones regulares, probablemente haya bibliotecas existentes para hacerlo, pero si quiere hacerlo usted mismo:
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));