Запрос вложенных данных в Postgres с использованием Node.js

При создании серверов, которые предоставляют данные JSON, используя REST или GraphQL, мы часто хотим запросить некоторое время вместе с его «дочерними элементами». Если мы не будем осторожны, мы можем получить большое количество неэффективных запросов. К счастью, в Postgres есть несколько функций, которые позволяют возвращать сложные структуры данных напрямую в виде «JSON». Это отлично работает с node.js, потому что они просто становятся собственными объектами JavaScript.

Эта проблема

Рассмотрим схему базы данных:

Мы можем создать эту схему базы данных вместе с некоторыми примерами данных, выполнив:

CREATE TABLE users (
  user_id BIGSERIAL NOT NULL PRIMARY KEY,
  display_name TEXT NOT NULL,
  manager_id BIGINT NULL REFERENCES users(user_id)
);
CREATE TABLE teams (
  team_id BIGSERIAL NOT NULL PRIMARY KEY,
  display_name TEXT NOT NULL
);
CREATE TABLE user_teams (
  user_id BIGINT NOT NULL REFERENCES users,
  team_id BIGINT NOT NULL REFERENCES teams,
  PRIMARY KEY(user_id, team_id)
);
INSERT INTO users
  (user_id, display_name, manager_id)
VALUES
  (1, 'Forbes', NULL), (2, 'John', NULL), (3, 'Joe', 1);
INSERT INTO teams
  (team_id, display_name)
VALUES
  (1, 'Awesome Team'), (2, 'Team of One');
INSERT INTO user_teams
  (user_id, team_id)
VALUES
  (1, 1), (2, 1), (1, 2);

Теперь представьте, что мы хотим выполнить такой запрос:

Получите список всех пользователей, и для каждого пользователя найдите его команды и их менеджеров

Нам нужен вывод, который выглядит примерно так:

[
  {
    "id": 1,
    "display_name": "Forbes",
    "manager": null,
    "teams": [
      {
        "id": 1,
        "display_name": "Awesome Team"
      },
      {
        "id": 2,
        "display_name": "Team of One"
      }
    ]
  },
  {
    "id": 2,
    "display_name": "John",
    "manager": null,
    "teams": [
      {
        "id": 1,
        "display_name": "Awesome Team"
      }
    ]
  },
  {
    "id": 3,
    "display_name": "Joe",
    "manager": {
      "id": 1,
      "display_name": "Forbes"
    },
    "teams": []
  }
]

Самое простое решение?

У нас есть несколько вариантов. Мы могли бы просто запустить все запросы рекурсивно, используя node.js, чтобы объединить все данные, используя @databases/pg:

const results = await Promise.all(
  (await db.query(
    sql`
      SELECT u.user_id AS id, u.display_name, u.manager_id
      FROM users u
    `,
  )).map(async ({manager_id, ...user}) => ({
    ...user,
    manager:
      (await db.query(
        sql`
          SELECT u.user_id AS id, u.display_name
          FROM users u
          WHERE u.user_id=${manager_id}
        `,
      ))[0] || null,
    teams: await db.query(
      sql`
        SELECT t.team_id AS id, t.display_name
        FROM user_teams ut
        JOIN teams t USING (team_id)
        WHERE ut.user_id = ${user.id}
      `,
    ),
  })),
);
console.log(results);

Это отлично работает, пока набор данных невелик. Единственная проблема в том, что мы выполняем 2n + 1 запросов (где n - количество пользователей). Мы также не можем выполнять внутренние запросы, пока не соберем всех пользователей. Это создает большую ненужную нагрузку как на наш сервер node.js, так и на наш сервер Postgres.

Выберите `ANY`

Если вы знакомы с JavaScript и немного знакомы с SQL, вы можете подумать, чтобы исправить это, сделав всего 3 запроса:

const users = await db.query(
  sql`
    SELECT u.user_id AS id, u.display_name, u.manager_id
    FROM users u
  `,
);
const userTeams = await db.query(
  sql`
    SELECT t.team_id AS id, t.display_name, ut.user_id AS user_id
    FROM user_teams ut
    JOIN teams t USING (team_id)
    WHERE ut.user_id = ANY(${users.map(u => u.id)})
  `,
);
const managers = await db.query(
  sql`
    SELECT u.user_id AS id, u.display_name
    FROM users u
    WHERE u.user_id = ANY(${users.map(u => u.manager_id)})
  `,
);

Это намного эффективнее, но нам еще предстоит проделать большую работу в области JavaScript, чтобы объединить эти результаты в желаемую вложенную структуру. Мы собрали все данные достаточно эффективно, но нам еще предстоит проделать большую часть работы.

JSON спешит на помощь!

Разве не было бы замечательно, если бы Postgres мог напрямую возвращать вложенные данные именно в том формате, который нам нужен? Хорошая новость в том, что это возможно!

Во-первых, нам нужно немного разобраться в типах данных.

Тип данных Record представляет строку данных SQL. На верхнем уровне все запросы SQL возвращают список Record. К сожалению, если мы попытаемся вернуть вложенные строки, они вернутся в виде неудобных строк, которые трудно декодировать во что-либо полезное.
Тип данных Array представляет собой список вещей, например Объекты JSON.
Тип данных JSON представляет объект или массив.

Мы собираемся использовать следующие функции для работы со всеми этими типами:

row_to_json принимает Record и возвращает JSON.
array_agg - это «функция агрегирования». Если вы используете это в SELECT запросе, вы получите одну запись, содержащую Array значений, вместо множества записей, каждая из которых содержит одно значение.
array_to_json принимает Array (например, результат array_agg) и преобразует его в JSON.
coalesce принимает два значения, и если первое значение равно NULL, оно принимает второе значение (приведение его к типу первого значения).

Обработка поля менеджера

Первый - row_to_json. Вы можете использовать подзапрос, который возвращает только одно значение в качестве поля в SQL. Все, что нам нужно сделать, это преобразовать строку в JSON, чтобы наше одно значение могло быть сложным объектом:

const results = await db.query(
  sql`
    SELECT
      u.user_id AS id,
      u.display_name,
      (
        SELECT row_to_json(x) FROM
        (
          SELECT m.user_id AS id, m.display_name
          FROM users m WHERE m.user_id = u.manager_id
        ) x
      ) AS manager
    FROM users u
  `,
);

Дополнительное вложение сделано для того, чтобы мы могли дать четкое имя x нашей записи и передать его в row_to_json.

Мы можем привести это в порядок с помощью вспомогательной функции:

function nestQuerySingle(query) {
  return sql`
    (SELECT row_to_json(x) FROM (${query}) x)
  `;
}

Тогда наш запрос становится:

const results = await db.query(
  sql`
    SELECT
      u.user_id AS id,
      u.display_name,
      ${nestQuerySingle(
        sql`
          SELECT m.user_id AS id, m.display_name
          FROM users m WHERE m.user_id = u.manager_id
        `
      )} AS manager
    FROM users u
  `,
);

N.B. Это вызовет ошибку в Postgres, если запрос менеджера когда-либо вернул несколько записей.

Работа с командами

Теперь наши Record преобразованы в JSON, но нам нужно исправить еще одну проблему, чтобы справиться с командами. Проблема в том, что вы не можете иметь несколько значений как часть одного поля. Вот тут-то и появляется array_agg, а также array_to_json и coalesce.

const results = await db.query(
  sql`
    SELECT
      u.user_id AS id,
      u.display_name,
      ${nestQuerySingle(
        sql`
          SELECT m.user_id AS id, m.display_name
          FROM users m WHERE m.user_id = u.manager_id
        `
      )} AS manager,
      coalesce(
        (
          SELECT array_to_json(array_agg(row_to_json(x)))
          FROM (
            SELECT t.team_id AS id, t.display_name
            FROM user_teams ut
            JOIN teams t USING (team_id)
            WHERE ut.user_id = u.user_id
          ) x
        ),
        '[]'
      ) AS teams
    FROM users u
  `,
);

Мы используем подзапрос, чтобы получить teams, затем мы используем array_to_json(array_agg(row_to_json(x))), чтобы преобразовать множество записей в одно значение JSON. К сожалению, Postgres вернет здесь NULL, если нет записей, а не [], поэтому нам нужно использовать coalesce, чтобы указать значение по умолчанию. Значение по умолчанию '[]' в качестве строки здесь автоматически приводится к JSON, чтобы соответствовать типу результата array_to_json.

Еще одна вспомогательная функция:

function nestQuery(query) {
  return sql`
    coalesce(
      (
        SELECT array_to_json(array_agg(row_to_json(x)))
        FROM (${query}) x
      ),
      '[]'
    )
  `;
}

и у нас есть последний запрос:

const results = await db.query(
  sql`
    SELECT
      u.user_id AS id,
      u.display_name,
      ${nestQuerySingle(
        sql`
          SELECT m.user_id AS id, m.display_name
          FROM users m WHERE m.user_id = u.manager_id
        `
      )} AS manager,
      ${nestQuery(
        sql`
          SELECT t.team_id AS id, t.display_name
          FROM user_teams ut
          JOIN teams t USING (team_id)
          WHERE ut.user_id = u.user_id
        `
      )} AS teams
    FROM users u
  `,
);

Это возвращает те же данные, что и наше «простое» решение с использованием запросов, вложенных в node.js, за исключением того, что теперь вложение происходит в SQL. Это будет намного более производительно, не говоря уже о более простом обслуживании.

Заключение

Возможно, вам понадобится несколько вспомогательных функций, но Postgres с @databases действительно хорошо подходит для запросов к вложенным структурам данных. Если вам понравилась эта статья, вам обязательно нужно нажать на кнопку «подписаться». Скоро я напишу статью о том, как выполнять рекурсивные запросы. например что, если бы я хотел поставить всех выше меня в иерархии менеджеров (то есть моего менеджера, и их менеджера, и их менеджера и т. д.).

смотрите также:

Новые материалы

Объяснение документов 02: BERT

BERT представил двухступенчатую структуру обучения: предварительное обучение и тонкая настройка. Во время предварительного обучения модель обучается на неразмеченных данных с помощью..

Как проанализировать работу вашего классификатора?

Не всегда просто знать, какие показатели использовать С развитием глубокого обучения все больше и больше людей учатся обучать свой первый классификатор. Но как только вы закончите..

Работа с цепями Маркова, часть 4 (Машинное обучение)

Нелинейные цепи Маркова с агрегатором и их приложения (arXiv) Автор : Бар Лайт Аннотация: Изучаются свойства подкласса случайных процессов, называемых дискретными нелинейными цепями Маркова..

Crazy Laravel Livewire упростил мне создание электронной коммерции (панель администратора и API) [Часть 3]

Как вы сегодня, ребята? В этой части мы создадим CRUD для данных о продукте. Думаю, в этой части я не буду слишком много делиться теорией, но чаще буду делиться своим кодом. Потому что..

Использование машинного обучения и Python для классификации 1000 сезонов новичков MLB Hitter

Чему может научиться машина, глядя на сезоны новичков 1000 игроков MLB? Это то, что исследует это приложение. В этом процессе мы будем использовать неконтролируемое обучение, чтобы..

Учебные заметки: создание моего первого пакета Node.js

Это мои обучающие заметки, когда я научился создавать свой самый первый пакет Node.js, распространяемый через npm. Оглавление Глоссарий I. Новый пакет 1.1 советы по инициализации..

Забудьте о Matplotlib: улучшите визуализацию данных с помощью умопомрачительных функций Seaborn!

Примечание. Эта запись в блоге предполагает базовое знакомство с Python и концепциями анализа данных. Привет, энтузиасты данных! Добро пожаловать в мой блог, где я расскажу о невероятных..

Метки

Machine Learning JavaScript Data Science Artificial Intelligence Software Development Python Web Development Coding Deep Learning AI React Software Engineering Nodejs Java Front End Development Typescript Computer Science Data Algorithms Development NLP Tech Programming Languages CSS ChatGPT HTML Python Programming Javascript Tips Angular Computer Vision Startup Data Visualization Neural Networks Tutorial Statistics Productivity Reactjs Learning

Запрос вложенных данных в Postgres с использованием Node.js