Как освоить конкатенацию строк в SQL для приложений OutSystems

Введение: задача «многие-к-одному»

Вы когда-нибудь сталкивались с необходимостью отобразить аккуратный список тегов, имен или категорий, разделенных запятыми, на одном экране? Это распространенный сценарий: у вас есть связь «один-ко-многим» в данных, и вы хотите представить все эти «многие» записи внутри одного чистого текстового поля.

Например, вы можете захотеть:

• Перечислить всех преподавателей, ведущих учебный курс.
• Показать несколько степеней, полученных студентом.
• Агрегировать электронные адреса клиентов для конкретного города.

Хотя вы могли бы получить основную запись и выполнять отдельные вызовы или циклы в логике клиентского уровня OutSystems, это быстро снижает производительность. Решение этой задачи непосредственно внутри базы данных с помощью Advanced SQL позволяет приложению работать быстро, избегать множественных сетевых запросов и сохранять логику фронтенда чистой.

Краткий анонс: хотите вообще не писать SQL вручную? OutSystems Mentor может создать весь экран, серверное действие и запрос к базе данных для вас менее чем за 2 минуты, используя естественный язык! Оставайтесь с нами — в конце этой статьи мы покажем, как именно происходит это волшебство!

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

Современный стандарт: STRING_AGG()

Если вы работаете с современной базой данных, STRING_AGG() — это ваш основной, наиболее эффективный и чистый метод конкатенации строк в одну.

Пошаговая реализация в OutSystems

• Создайте структуру: добавьте новую структуру в OutSystems с одним текстовым атрибутом для хранения вашей конкатенированной строки.
• Определите выходные данные запроса: включите целевую сущность вместе с созданной структурой в выходные параметры вашего запроса Advanced SQL.
• Напишите подзапрос JOIN: создайте подзапрос с использованием STRING_AGG() для группировки и объединения записей. STRING_AGG() удобно игнорирует значения NULL, поэтому у вас не будет лишних запятых в конце.
• Назначьте псевдоним: дайте конкатенированному столбцу явный псевдоним (например, INSTRUCTORS_LIST_IN_CSV), соответствующий атрибуту вашей структуры, и убедитесь, что сам подзапрос имеет псевдоним (например, TEMPSUBQUERY).
• Добавьте псевдоним: добавьте псевдоним столбца в основной оператор SELECT, чтобы сопоставить его с вашей структурой.

Пример кода

Примечание: обе базы данных используют STRING_AGG(). Однако при сортировке конкатенированных элементов PostgreSQL помещает предложение ORDER BY непосредственно внутрь аргументов STRING_AGG(), тогда как SQL Server требует добавления предложения WITHIN GROUP (ORDER BY ...) к функции.

Вывод запроса

Вот пример того, как будут выглядеть данные, полученные в результате этого запроса:

101

Введение в информатику

Алан Тьюринг, Грейс Хоппер

102

История современного искусства

Боб Росс, Фрида Кало, Пабло Пикассо

103

Математический анализ I

Исаак Ньютон

104

Самостоятельная работа

NULL

Устаревший обходной путь: STUFF() и FOR XML PATH()

Если вы поддерживаете среды OutSystems 11, подключенные к SQL Server 2016 или более старым версиям, STRING_AGG() будет недоступен и вызовет ошибку.

В SQL Server разработчики исторически полагались на STUFF() в сочетании с FOR XML PATH().

Как это работает

• FOR XML PATH() объединяет значения в одну строку в формате XML. Однако он оставляет нежелательный начальный разделитель (например, ,) и кодирует специальные символы, что означает, что & превратится в &.
• STUFF() удаляет начальную запятую и пробел, удаляя два символа, начиная с позиции 1.
• Добавление .value('(./text())[1]', 'varchar(max)') декодирует любые экранированные XML-сущности обратно в обычный текст.

Пример кода

Примечание: PostgreSQL поддерживает STRING_AGG() нативно начиная с версии 9.0, что означает, что устаревшие обходные пути, такие как FOR XML PATH() в SQL Server, никогда не нужны в экземплярах PostgreSQL.

Примечание: нам нужно ', ' +, чтобы подзапрос не склеивал названия степеней вместе, что привело бы к сплошному блоку текста без пробелов или пунктуации.

Вывод запроса

Вот пример того, как будут выглядеть данные, полученные в результате этого запроса:

500

Сара Коннор

Бакалавр искусств, Магистр делового администрирования

501

Тони Старк

Бакалавр инженерии, Магистр наук, Доктор физики

502

Брюс Уэйн

NULL

Сравнение: STRING_AGG() против STUFF() + FOR XML PATH()

Оба подхода справляются с задачей, но они существенно различаются по синтаксису, производительности и совместимости.

Плюсы

✅ Более простой и чистый синтаксис. ✅ Лучшая производительность (нет парсинга XML). ✅ Автоматическая обработка NULL.

✅ Совместимость со старыми версиями SQL Server (2005+). ✅ Высокая гибкость управления подзапросами.

Минусы

❌ Требуются современные SQL-движки (SQL Server 2017+ или PostgreSQL)

❌ Сложный, трудночитаемый синтаксис. ❌ Медленнее на больших наборах данных. ❌ Требуется ручное декодирование XML-символов.

Альтернатива API: структурирование данных как JSON

При создании бэкенд-эндпоинтов или подготовке записей для REST API вам часто нужен структурированный JSON-массив, а не простая строка, разделенная запятыми.

Как этого достичь

• SQL Server использует встроенное предложение FOR JSON PATH для преобразования результатов запроса в JSON-строки.
• PostgreSQL использует JSON-функции, такие как json_agg() в сочетании с json_build_object() или row_to_json(), для возврата структурированных JSON-массивов.

Вариант использования 1 — конкатенация в столбец JSON-строки

Если вы хотите, чтобы вывод вашего запроса содержал стандартные столбцы наряду с одним атрибутом, содержащим строку JSON-массива:

Примечание: в PostgreSQL json_agg() агрегирует вложенные строки в JSON-массив, а json_build_object() форматирует каждую строку в пары ключ-значение. Функция COALESCE() получает валидную (хотя и пустую) JSON-строку, что в дальнейшем позволяет избежать ошибок «cannot read property of null».

Примечание: в SQL Server 2016 и более поздних версиях FOR JSON PATH выполняет форматирование неявно на основе имен столбцов.

Когда ни одна строка не соответствует условию, весь подзапрос возвращает 0 строк, что на уровне скалярного подзапроса оценивается как NULL. Таким образом, COALESCE должен находиться вне подзапроса:

Вариант использования 1 — пример вывода

500

501

502

Вариант использования 2 — генерация полного API-пейлоада

Если вы хотите обернуть весь набор данных в единый структурированный JSON-ответ непосредственно из базы данных:

Примечание: json_build_object('Students', ...) используется для того, чтобы обернуть массив верхнего уровня внутри внешнего объекта.

Примечание: SQL Server использует предложение ROOT('Students') для того, чтобы обернуть массив верхнего уровня внутри внешнего объекта.

JSON_QUERY() гарантирует, что SQL Server будет обрабатывать вложенный массив как «сырой» JSON, а не как экранированную строку.

Вариант использования 2 — пример вывода

Этот запрос создает полный JSON, выглядящий так:

Подводя итог, FOR JSON PATH создает стандартный формат, используемый большинством веб-API, где вывод требует структурированных, вложенных данных, а не плоской строки с разделителями.

Главный лайфхак по скорости: позвольте OutSystems Mentor сделать это за вас

Теперь, когда вы понимаете, как эти запросы работают «под капотом», давайте посмотрим, как вы можете

полностью обойти ручное написание кода Advanced SQL.

С помощью OutSystems Mentor, ИИ-помощника в OutSystems Developer Cloud (ODC), вы можете спроектировать и реализовать эту функцию менее чем за две минуты, используя простые подсказки на естественном языке, например, просто набрав: «Создай экран, который перечисляет всех преподавателей для каждого курса, но показывает одну строку на курс, используя Advanced SQL для оптимизации выборки данных».

Что делает Mentor

Когда вы пишете вышеуказанную подсказку, Mentor не просто пишет фрагмент кода — он создает всю функцию целиком:

• Создает экран пользовательского интерфейса: генерирует макет таблицы фронтенда со всеми необходимыми элементами управления.
• Определяет структуру: автоматически создает структуру выходных данных, необходимую для агрегированного списка.
• Реализует серверное действие: генерирует оптимизированный, готовый к промышленной эксплуатации Advanced SQL, специально адаптированный для движка базы данных Aurora PostgreSQL в ODC.

Вот чистый и эффективный код, который Mentor сгенерировал для группировки записей и объединения строк:

Почему подход Mentor впечатляет

Mentor следует лучшим практикам корпоративной разработки:

• STRING_AGG(): корректно агрегирует имена инструкторов в аккуратную CSV-строку.
• COALESCE(): предотвращает появление значений NULL на экранах, возвращая пустые строки для курсов без назначенных инструкторов.
• Умные соединения (Smart Joins): использует цепочки LEFT JOIN, чтобы курсы без инструкторов все равно отображались в списке.

Сравнивайте, учитесь и итерируйте

Более того, если у вас есть собственный черновик SQL, вы можете попросить Mentor провести сравнительный анализ. Он разберет различия в поведении соединений, эффективности запросов, компромиссах в производительности и читаемости, чтобы вы могли учиться в процессе создания!

Используя естественный язык, вы экономите время, видите обоснованные технические подходы и даже можете запрашивать сравнения. Именно так OutSystems превращает генерацию ИИ в критически важное программное обеспечение — быстро принося бизнес-ценность и сохраняя гибкость для настройки.

Заключение: выбирайте правильный инструмент для задачи

В этой статье были рассмотрены мощные SQL-техники, которые можно использовать для освоения конкатенации строк: от современных стандартов до устаревших обходных путей и решений, ориентированных на API.

Понимая эти методы, вы сможете эффективно извлекать связанные данные и создавать высокопроизводительные и отзывчивые приложения OutSystems. В зависимости от вашей настройки:

• Используйте STRING_AGG() для современных баз данных (ODC PostgreSQL или SQL Server 2017+).
• Используйте STUFF() + FOR XML PATH() при поддержке устаревших баз данных SQL Server.
• Используйте функции агрегации JSON (json_agg или FOR JSON PATH) при создании моделей REST API.
• Используйте OutSystems Mentor всякий раз, когда хотите превратить естественный язык в полнофункциональное, критически важное программное обеспечение за считанные секунды.

Присоединяйтесь к сообществу OutSystems, чтобы обсуждать инструкции, задавать вопросы, общаться с профессионалами OutSystems и многое другое!

Фабио обладает 12-летним опытом работы в рекламе и маркетинге, а теперь его движет глубокая страсть к ИТ, цифровой стратегии и UX. Он известен своим структурированным мышлением, логическим обоснованием и тщательным вниманием к деталям, отточенным благодаря строгому кодированию и обучению в OutSystems. Будучи искусным коммуникатором, Фабио преуспевает в обмене знаниями и обучении коллег для достижения совместного успеха.

Посмотреть все публикации этого автора