FOR JSON - scrappyCoco/SQL-Note GitHub Wiki

В SQL Server 2016 была введена поддержка формирования JSON. Радости было нескончаемое количество. Думал, что светлое будущее наступило. Но каково было мое расстройство, когда захотел сформировать простой массив строк... SQL:

DECLARE @groups TABLE (
  GroupId   INT PRIMARY KEY,
  GroupName VARCHAR(100)
);

DECLARE @users TABLE (
  UserId   INT PRIMARY KEY,
  UserName VARCHAR(100)
);

DECLARE @userOfGroup TABLE (
  UserId  INT,
  GroupId INT,
  PRIMARY KEY (UserId, GroupId)
);

INSERT INTO @groups (GroupId, GroupName)
VALUES
  (1, '.NET'),
  (2, 'Java')

INSERT INTO @users (UserId, UserName)
VALUES
  (1, 'Artem'),
  (2, 'Julia'),
  (3, 'Mary')

INSERT INTO @userOfGroup (UserId, GroupId)
VALUES
  (1, 1), -- Artem - .NET
  (2, 1), -- Julia - .NET
  (2, 2), -- Julia - Java
  (3, 1), -- Mary - .NET
  (3, 2); -- Mary - Java

SELECT
  groupId   = Groups.GroupId,
  groupName = Groups.GroupName,
  members   = (
                SELECT
                  userName = Users.UserName
                FROM @userOfGroup AS UserOfGroup
                     INNER JOIN @users AS Users ON UserOfGroup.UserId = Users.UserId
                WHERE
                  UserOfGroup.GroupId = Groups.GroupId FOR JSON PATH
              )
FROM @groups AS Groups FOR JSON PATH

Результат:

[
  {
    "groupId": 1,
    "groupName": ".NET",
    "members": [
      {
        "userName": "Artem"
      },
      {
        "userName": "Julia"
      },
      {
        "userName": "Mary"
      }
    ]
  },
  {
    "groupId": 2,
    "groupName": "Java",
    "members": [
      {
        "userName": "Julia"
      },
      {
        "userName": "Mary"
      }
    ]
  }
]

А хотелось:

[
  {
    "groupId": 1,
    "groupName": ".NET",
    "members": [
      "Artem",
      "Julia",
      "Mary"
    ]
  },
  {
    "groupId": 2,
    "groupName": "Java",
    "members": [
      "Julia",
      "Mary"
    ]
  }
]

Решения как сформировать простейший массив строк так и не нашёл. Печаль...