Оптимизация JSON полученного из таблиц

@sevaor · Регистрация: 23.01.2021

Студворк — интернет-сервис помощи студентам

Доброго времени суток!
Имеется две связанных таблицы. Одна с наименованиями продуктов, вторая с штрих-кодами для этих продуктов. У одного продукта м. б. несколько штрих-кодов или не быть вообще.

Продукты

ID (prdct_ID)	Наименование (prdct_Name)
1	Томатная паста Байсад 250гр
2	Майонез Байсад 250гр
3	Чай Акбар, 100 пак.
4	Масло слив. Буренка, 200гр
5	Конфеты Маска, 500гр
6	Молоко Буренка 3.5%, 0,9л
7	Молоко Буренка 2.5%, 0,9л

Штрих-коды

ID штрих-кода (idntfr_ID)	ID продукта (idntfr_prdct_ID)	Штрих-код (idntfr_Code)
1	1	45820003781233301
3	1	45847000353301
2	1	4582000374353301
4	2	49000000353301
5	2	4900000035111101
6	2	4977700035111101
7	5	8900000035111101
8	5	890000099343111101
9	5	8565656000099343111101
10	4	85690099343111101
11	4	85690099343113232323
12	3	11232099343113232323
13	3	454545339343113232323

Задача получить JSON с данными из двух таблиц вида:

JSON
1
2
3
4
5
6
7
[{"prdct_ID":1,"prdct_Name":"Томатная паста Байсад 250гр","prdct_Codes":["45820003781233301","45847000353301","4582000374353301"]},
 {"prdct_ID":2,"prdct_Name":"Майонез Байсад 250гр","prdct_Codes":["49000000353301","4900000035111101","4977700035111101"]},
 {"prdct_ID":3,"prdct_Name":"Чай Акбар, 100 пак.","prdct_Codes":["11232099343113232323","454545339343113232323"]},
 {"prdct_ID":4,"prdct_Name":"Масло слив. Буренка, 200гр","prdct_Codes":["85690099343111101","85690099343113232323"]},
 {"prdct_ID":5,"prdct_Name":"Конфеты Маска, 500гр","prdct_Codes":["8900000035111101","890000099343111101","8565656000099343111101"]},
 {"prdct_ID":6,"prdct_Name":"Молоко Буренка 3.5%, 0,9л"},
 {"prdct_ID":7,"prdct_Name":"Молоко Буренка 2.5%, 0,9л"}]

У меня получается следующее:

SQL
1
2
3
4
SELECT prdct_ID, prdct_Name, prdct_Codes = (SELECT idntfr_Code FROM ProductIdentifiers WHERE idntfr_prdct_ID = prdct_ID FOR JSON AUTO) 
  FROM Products
  FOR JSON AUTO;
GO

И JSON вида:

JSON
1
2
3
4
5
6
7
[{"prdct_ID":1,"prdct_Name":"Томатная паста Байсад 250гр","prdct_Codes":[{"idntfr_Code":"45820003781233301"},{"idntfr_Code":"45847000353301"},{"idntfr_Code":"4582000374353301"}]},
 {"prdct_ID":2,"prdct_Name":"Майонез Байсад 250гр","prdct_Codes":[{"idntfr_Code":"49000000353301"},{"idntfr_Code":"4900000035111101"},{"idntfr_Code":"4977700035111101"}]},
 {"prdct_ID":3,"prdct_Name":"Чай Акбар, 100 пак.","prdct_Codes":[{"idntfr_Code":"11232099343113232323"},{"idntfr_Code":"454545339343113232323"}]},
 {"prdct_ID":4,"prdct_Name":"Масло слив. Буренка, 200гр","prdct_Codes":[{"idntfr_Code":"85690099343111101"},{"idntfr_Code":"85690099343113232323"}]},
 {"prdct_ID":5,"prdct_Name":"Конфеты Маска, 500гр","prdct_Codes":[{"idntfr_Code":"8900000035111101"},{"idntfr_Code":"890000099343111101"},{"idntfr_Code":"8565656000099343111101"}]},
 {"prdct_ID":6,"prdct_Name":"Молоко Буренка 3.5%, 0,9л"},
 {"prdct_ID":7,"prdct_Name":"Молоко Буренка 2.5%, 0,9л"}]

Не могу понять как правильно сократить JSON и убрать ненужные заголовки ключей "idntfr_Code".

Скрипт на создание таблиц

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/****** Object:  Table [dbo].[ProductIdentifiers]    Script Date: 12.08.2022 19:00:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductIdentifiers](
    [idntfr_ID] [INT] NOT NULL,
    [idntfr_prdct_ID] [INT] NOT NULL,
    [idntfr_Code] [nvarchar](MAX) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Products]    Script Date: 12.08.2022 19:00:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
    [prdct_ID] [INT] NOT NULL,
    [prdct_Name] [nvarchar](MAX) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

@invm · 13.08.2022, 14:46

T-SQL
1
2
3
4
5
6
7
8
9
10
11
declare @t1 table (t1_id int);
declare @t2 table (t2_id int, t1_id int);
 
insert into @t1 values (1), (2);
insert into @t2 values (10, 1), (20, 1), (30, 2), (40, 2);
 
select
 a.t1_id, json_query(b.s) as t2_list
from @t1 a
outer apply (select concat('[', string_agg(t2_id, ','), ']') from @t2 where t1_id = a.t1_id) b(s)
for json path;

@sevaor · 13.08.2022, 23:46 **[ТС]**

invm, спасибо!
На основе вашего решения сделал такой запрос:

SQL
1
2
3
4
SELECT prdct_ID, prdct_Name, JSON_QUERY((SELECT CONCAT('[', STRING_AGG('"'+idntfr_Code+'"', ','), ']') FROM ProductIdentifiers WHERE idntfr_prdct_ID = prdct_ID)) AS prdct_Codes
  FROM Products
  FOR JSON AUTO;
GO

Вроде бы все как надо, но из-за того что формирование JSON ключей prdct_Codes эмулируется на лету для каждой записи из таблицы Products, это происходит даже для записей в которых нет ссылки на таблицу ProductIdentifiers. Иными словами, для продуктов у которых нет штрих-кодов, все равно формируется пустой ключ prdct_Codes:

JSON
1
2
3
4
5
6
7
[{"prdct_ID":1,"prdct_Name":"Томатная паста Байсад 250гр","prdct_Codes":["45820003781233301","45847000353301","4582000374353301"]},
 {"prdct_ID":2,"prdct_Name":"Майонез Байсад 250гр","prdct_Codes":["49000000353301","4900000035111101","4977700035111101"]},
 {"prdct_ID":3,"prdct_Name":"Чай Акбар, 100 пак.","prdct_Codes":["11232099343113232323","454545339343113232323"]},
 {"prdct_ID":4,"prdct_Name":"Масло слив. Буренка, 200гр","prdct_Codes":["85690099343111101","85690099343113232323"]},
 {"prdct_ID":5,"prdct_Name":"Конфеты Маска, 500гр","prdct_Codes":["8900000035111101","890000099343111101","8565656000099343111101"]},
 {"prdct_ID":6,"prdct_Name":"Молоко Буренка 3.5%, 0,9л","prdct_Codes":[]},
 {"prdct_ID":7,"prdct_Name":"Молоко Буренка 2.5%, 0,9л","prdct_Codes":[]}]

@invm · 14.08.2022, 11:23

T-SQL
1
2
3
4
5
select
 a.t1_id, json_query('[' + b.s + ']') as t2_list
from @t1 a
outer apply (select string_agg(t2_id, ',') from @t2 where t1_id = a.t1_id) b(s)
for json path;

@sevaor · 14.08.2022, 20:27 **[ТС]**

invm, спасибо за помощь, сам не догадался.

Новые блоги и статьи Все статьи Все блоги /
http://iceja.net/ сервер решения полиномов iceja 18.01.2026 Выкатила http:/ / iceja. net/ сервер решения полиномов (находит действительные корни полиномов методом Штурма). На сайте документация по API, но скажу прямо VPS слабенький и 200 000 полиномов. . .	Первый деплой lagorue 16.01.2026 Не спеша развернул своё 1ое приложение в kubernetes. А дальше мне интересно создать 1фронтэнд приложения и 2 бэкэнд приложения развернуть 2 деплоя в кубере получится 2 сервиса и что-бы они. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь постоянного тока с R, L, C, k(ключ), U, E, J. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа, решает её и находит: токи, напряжения и их 1 и 2 производные при t = 0;. . .	Восстановить юзерскрипты Greasemonkey из бэкапа браузера damix 15.01.2026 Если восстановить из бэкапа профиль Firefox после переустановки винды, то список юзерскриптов в Greasemonkey будет пустым. Но восстановить их можно так. Для этого понадобится консольная утилита. . .
Изучаю kubernetes lagorue 13.01.2026 А пригодятся-ли мне знания kubernetes в России?	Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .

@sevaor 3 / 3 / 1 Регистрация: 23.01.2021 Сообщений: 16
	14.08.2022, 20:27 [ТС]
	invm, спасибо за помощь, сам не догадался. 0