How to return array of objects as nested field from sql query?

ghz 8months ago ⋅ 89 views

I'm quite new to sql so I don't quite know what is the correct approach to fix my problem. I have two tables, events and labels that have many-to-many relations on id field. i also create junction table for them.

I want to get data from tables in following format:

it should be an array of events and each event should have labels field, which is a list of labels for this event

[
 {
  id: 'event1',
  labels: [
   { id: 'label1'},
   { id: 'label2'},
 },
 {
  id: 'event2',
  labels: [
   { id: 'label3'},
   { id: 'label4'},
 }
]

what is the best way to achieve it? should i write some subquery or should i do split the query into two separate and just combine them later?

I'm using node, drizzle and postgres

i tried drizzle query and select syntax but couldn't achieve needed result

here's my drizzle schemas

const events = pgTable('events', {
  id: uuid('id').primaryKey(),
  userId: uuid('user_id')
    .notNull()
    .references(() => authUsers.id, { onDelete: 'cascade' }),
  description: text('description').notNull(),
  dueDate: timestamp('due_date'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
})

const eventsRelations = relations(events, ({ many }) => ({
  eventsToLabels: many(eventsToLabels),
}))

const labels = pgTable('labels', {
  id: uuid('id').primaryKey(),
  label: text('label').notNull(),
  description: text('description'),
  userId: uuid('user_id').references(() => authUsers.id, {
    onDelete: 'cascade',
  }),
})

const labelsRelations = relations(labels, ({ many }) => ({
  eventsToLabels: many(eventsToLabels),
}))

const eventsToLabels = pgTable(
  'event_label',
  {
    eventId: uuid('event_id')
      .notNull()
      .references(() => events.id, { onDelete: 'cascade' }),
    labelId: uuid('label_id')
      .notNull()
      .references(() => labels.id, { onDelete: 'cascade' }),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.eventId, table.labelId] }),
  }),
)

Answers

To achieve the desired result of fetching data from the events table along with their associated labels in the format you specified, you can use a combination of SQL queries and data manipulation in your Node.js application.

Here's a general approach you can take:

  1. Write a SQL query to fetch data from the events table along with their associated labels.
  2. Use JOINs to combine the necessary tables (events, events_label, and labels).
  3. Group the data by event ID and aggregate the labels for each event.
  4. In your Node.js application, execute the SQL query and process the results to format them as needed.

Here's an example SQL query that you can use:

SELECT
    e.id AS event_id,
    json_agg(json_build_object('id', l.id)) AS labels
FROM
    events e
JOIN
    event_label el ON e.id = el.event_id
JOIN
    labels l ON el.label_id = l.id
GROUP BY
    e.id;

This query fetches all events along with their associated labels and aggregates the labels into a JSON array using json_agg and json_build_object.

In your Node.js application, you can execute this query using your preferred PostgreSQL client library (such as pg for Node.js) and process the results to match the desired format.

Here's a simplified example using pg library:

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: 'your_database_connection_string',
});

pool.query(
  'SELECT e.id AS event_id, json_agg(json_build_object(\'id\', l.id)) AS labels FROM events e JOIN event_label el ON e.id = el.event_id JOIN labels l ON el.label_id = l.id GROUP BY e.id;',
  (err, res) => {
    if (err) {
      console.error('Error executing query:', err);
      return;
    }
    
    const eventsWithLabels = res.rows.map(row => ({
      id: row.event_id,
      labels: row.labels
    }));

    console.log(eventsWithLabels);
  }
);

In this example, eventsWithLabels will contain an array of events, where each event object has an id field and a labels field containing an array of label objects. Adjust the code according to your specific requirements and application architecture.

#sql