Presenting query results as JS objects to client code

[acarstoiu]

Given that PostgreSQL has natural support for array and records, I would like to materialise one-to-many relationships in JS directly from the query result, without subsequent processing.

Let's say that an entity A owns multiple B entities. Normally, such data are brought in memory with a query like this:

select *
from a join b on b.a_id = a.id
where ...

The returned result set requires further processing to convert it to an array of A entities each carrying in a property the array of owned Bs 👎🏻

I'm interested in a query built along the lines of this:

select a.*, array( select b from b where b.a_id = a.id ) as bs
from a
where ...

Unfortunately, the database returns in column bs (no bullshit!) string literals of arrays of records, while I would like to be handed an actual JS array with actual JS objects as items. I suppose this can be done on the client side, since you also receive the result set metadata.
What do you think (about saying bye-bye to suboptimal ORMs)❓

[porsager]

I said bye-bye to ORMs long ago (not even sure I ever really said hello)... 😉

Look into PostgreSQL features for JSON which will allow you to return exactly the result you want ☺️ I have many queries that return nested json structures

Eg:

select
  name,
  json_agg(p) as posts
from users u
left join lateral (
  select
    created_at,
    title,
    content
  from posts
  where user_id = u.user_id
) p on true

[acarstoiu]

Thanks for the tip.
I intended something else, namely to take advantage of array and struct data types in Postgres, but I'll look into your method and see how far it takes me 🙂

[acarstoiu]

Right, in case anyone else is interested, this would be it (entity A owns multiple B entities):

select a.*, json_agg(b) as bs
from a,
lateral (select * from b  where b.a_id = a.id) b
group by a.id

Filtering and sorting can be added as necessary, including in the inner select. Enjoy 🎉