Field as Json array

user_12073359 0
Viewed 33

I have below tables structure where it has one to many mappings

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')

I m trying to produce below Json out of this

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

I have tried below so far. Joining with one table have expected output structure, but when the second table joined it get messy

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
sql-server-2016 tsql sql-server sql json
1 Answers

You'll want to use a couple of sub queries in the SELECT here get the results you're after:

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<>fiddle

user_20299830
Related