Блог

У меня есть структура таблиц ниже, где она имеет от одного до многих сопоставлений

 CREATE TABLE #Primary(PrimaryId int,ItemName varchar(10))
CREATE TABLE #Sub1(PrimaryId int,SubId int,SubName1 varchar(10))
CREATE TABLE #Sub2(PrimaryId int,Sub2Id int,SubName2 varchar(10))


INSERT INTO #Primary
VALUES(1,'My Item1'),(2,'My Item2')

INSERT INTO #Sub1 values(1,1,'Sub1'),(1,2,'Sub2')

INSERT INTO #Sub2 values(1,1,'Name1'),(1,2,'Name2')
 

Я пытаюсь создать ниже Json из этого

 [
  {
    "PrimaryId": 1,
    "ItemName": "My Item1",
    "sb1": [
      {
        "SubId": 1,
        "SubName1": "Sub1"
      },
      {
        "SubId": 2,
        "SubName1": "Sub2"
      }
    ],
    "sb2": [
      {
        "Sub2Id": 1,
        "SubName2": "Name1"
      },
      {
        "Sub2Id": 2,
        "SubName2": "Name2"
      }
    ]
  }
]
 

Я уже пробовал ниже. Объединение с одной таблицей имеет ожидаемую структуру вывода, но когда вторая таблица присоединилась, она стала беспорядочной

 select p.PrimaryId,p.ItemName,sb1.SubId,sb1.SubName1,sb2.Sub2Id,sb2.SubName2
from #Primary p
    inner join #Sub1 sb1 on p.PrimaryId = sb1.PrimaryId
    inner join #Sub2 sb2 on p.PrimaryId = sb2.PrimaryId
FOR JSON AUTO
 

Вы захотите использовать пару вложенных запросов в SELECT here, чтобы получить нужные вам результаты:

 SELECT P.PrimaryID,
       P.ItemName,
       (SELECT S1.SubId,
               S1.SubName1
        FROM #Sub1 S1
        WHERE S1.PrimaryId = P.PrimaryId
        FOR JSON AUTO) AS sb1,
       (SELECT S2.Sub2Id,
               S2.SubName2
        FROM #Sub2 S2
        WHERE S2.PrimaryId = P.PrimaryId
        FOR JSON AUTO) AS sb2
FROM #Primary P
WHERE P.PrimaryID = 1
FOR JSON AUTO;
 

db<>скрипка