Returning nested JSON from many to many joined table from PostgreSQL to node.js

Question

can anyone help me with querying many to many relation tables in postgres?

i have tables:

> 1.exercise(id,name)
> 2.tag(id,label)
> 3.tag_in_exercise(id,exercise_id,tag_id)

let say, that we have one exercise bonded with two tags via tag_in_exercise

when using query :

select e.id,t.label from exercise e 
left join tag_in_exercise te on e.id=te.exercise_id  
left join tag t on te.tag_id=t.id

i will receive json

[ { id: 1,
    label: 'basic1' },
  { id: 1,
    label: 'basic2' }]

but i want to receive it as nested json

[ { id: 1,
    tags:[ {'basic1'},{'basic2'} ]
}]

is it possible to get that by using standart postgresql queries or i need to use some ORM?

or if exists another solution please let me know,

thanks

Answer 1

PostgreSQL does not return the JavaScript object you have posted. Your node driver is converting an array of arrays returned by PostgreSQL, which the driver is converting to JavaScript objects.

However, you can have PostgreSQL return a structure which I suspect will be converted how you wish by using array_agg.

Try this:

SELECT e.id,array_agg(t.label) AS label FROM exercise e LEFT JOIN tag_in_exercise te on e.id=te.exercise_id LEFT JOIN tag t on te.tag_id=t.id GROUP BY e.id;

You will get a raw PostgreSQL result in the structure you want, which hopefully the driver will translate as you intend:

id | label
----+----------------- 1 | {basic1,basic2} 2 | {NULL}