Создайте пользовательскую функцию для возврата иерархических данных в виде JSON

Это часть этого вопроса. У меня есть таблица Departments, которая использует HierarchyId для хранения иерархических данных.

ИдентификаторHieararchyIdИмя
1/МГц
2/2/Финансы
3/3/ЭТО
4/3/4/Разработка программного обеспечения
5/3/5/контроль качества
6/2/6/Бухгалтер

Я хочу получить эту таблицу как JSON для моего В виде дерева на лицевой стороне.

Что я сделал:

CREATE FUNCTION dbo.GetDepartmentAsJson
    (@departmentId hierarchyid, 
     @IsRoot int)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Json NVARCHAR(MAX) = '{}',
            @Id int,
            @Name varchar(50),
            @Children NVARCHAR(MAX) = '{}',
            @Hierar Hierarchyid

    SET @Json = (SELECT 
                     t.Id, t.HierarchyId, t.Name,
                     children = JSON_QUERY(dbo.GetDepartmentAsJson(t.HierarchyId, 2))
                 FROM Departments t
                 WHERE t.HierarchyId <> @departmentId
                   AND t.HierarchyId.IsDescendantOf(@departmentId) = 1
                 FOR JSON PATH);

    IF (@IsRoot = 1) 
    BEGIN
        SELECT
            @Id = t.Id,
            @Hierar = t.HierarchyId,
            @Name = t.Name
        FROM 
            Departments t
        WHERE 
            t.HierarchyId = @departmentId;

        SET @Json = 
        '{"Id":"' + CONVERT(varchar(7), @Id) +
        '","HierarchyId":"' + @Hierar.ToString() + 
        '","Name":"' + @Name + 
        '","Children":' + CAST(@Json AS NVARCHAR(MAX)) + '}';
        SET @IsRoot = 2;
    END

    RETURN @Json;
END;

Результат:

 {
  "Id": "1",
  "HierarchyId": "/",
  "Name": "MHz",
  "Children": [
    {
      "Id": 2,
      "HierarchyId": "/2/",
      "Name": "Finance",
      "Children": [
        {
          "Id": 6,
          "HierarchyId": "/2/6/",
          "Name": "Accountant"
        }
      ]
    },
    /* This should not be listed because already included as a child in Finance */
    {
      "Id": 6,
      "HierarchyId": "/2/6/",
      "Name": "Accountant"
    },
    {
      "Id": 3,
      "HierarchyId": "/3/",
      "Name": "IT",
      "Children": [
        {
          "Id": 4,
          "HierarchyId": "/3/4/",
          "Name": "Software Development"
        },
        {
          "Id": 5,
          "HierarchyId": "/3/5/",
          "Name": "QA"
        }
      ]
    },
    /* They both should not be listed because already included as a child in IT */
    {
      "Id": 4,
      "HierarchyId": "/3/4/",
      "Name": "Software Development"
    },
    {
      "Id": 5,
      "HierarchyId": "/3/5/",
      "Name": "QA"
    }
  ]
}

Как я могу исключить эти дочерние элементы, которые уже были перечислены в родительском элементе?

3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
0
0
30
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы также должны проверить уровень потомка

CREATE FUNCTION dbo.GetDepartmentAsJson(@departmentId hierarchyid, @level int)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Json NVARCHAR(MAX) = '{}',
    @Id int,
    @Name varchar(50),
    @Childeren NVARCHAR(MAX) = '{}',
    @Hierar Hierarchyid

    SET @Json = (SELECT
    t.Id,
    t.HierarchyId,
    t.Name,
    children = JSON_QUERY(dbo.GetDepartmentAsJson(t.HierarchyId, @level + 1))
    FROM Departments t
    WHERE t.HierarchyId <> @departmentId
    AND t.HierarchyId.IsDescendantOf(@departmentId) = 1 and t.HierarchyId.GetLevel() =  @level + 1
    FOR JSON PATH);

    IF(@level = 0) 
    BEGIN
        SELECT
        @Id = t.Id,
        @Hierar = t.HierarchyId,
        @Name = t.Name
        FROM Departments t
        WHERE t.HierarchyId = @departmentId;

        SET @Json = 
        '{"Id":"' + CONVERT(varchar(7), @Id) +
        '","HierarchyId":"' + @Hierar.ToString() + 
        '","Name":"' + @Name + 
        '","Children":' + CAST(@Json AS NVARCHAR(MAX)) + '}';
    END

    return @Json;

  END;

Корень иерархии — уровень 0, так что select dbo.GetDepartmentAsJson ('/', 0)

дб <> рабочий пример

Другие вопросы по теме