Obtenir des données hiérarchiques en JSON à partir de SQL Server

Question

J'ai le tableau suivant :

Id

HieararchyId

Nom

1

/

MHz

2

/1/

Finances

3

/1/

IT

4

/1/3/

Développement de logiciels

5

/1/3/

AQ

6

/1/2/

Comptable

et je veux obtenir des données pour mon TreeView comme :

[
{
  "Id": 1,
  "HierarchyId": "/"
  "Name":"MHz",
  "Children":[
     {
        "Id": 3,
        "HierarchyId": "/1/"
        "Name":"IT",
        "Children":[
           {
              "Id": 4,
              "HierarchyId": "/1/3/"
              "Name":"Software Development",
              "Children":[]
           }
           {
              "Id": 5,
              "HierarchyId": "/1/3/"
              "Name":"QA",
              "Children":[]
           }
        ]
     },
     {
        "Id": 2,
        "HierarchyId": "/1/"
        "Name":"Finance",
        "Children":[
           {
              "Id": 6,
              "HierarchyId": "/1/2/"
              "Name":"Accountant",
              "Children":[]
           }
        ]
     }
   ]
 }
]

J'ai essayé avec ça : Comment générer des données JSON hiérarchiques ?

J'ai trouvé erreur : Plus aucune classe de verrouillage n'est disponible à partir de la transaction.

Ou bien est-il préférable de créer une fonction récursive et de remplir la fonction Tree sur client side ?

Mise à jour

CREATE FUNCTION dbo.GetJson (@departmentId hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
RETURN (
    SELECT
      Id,
      HierarchyId,
      Name,
      children = JSON_QUERY(dbo.GetJson(HierarchyId))
    FROM Departments p
    WHERE p.HierarchyId.IsDescendantOf(@departmentId ) = 1
    FOR JSON PATH
);
END;

Answer 1

Le meilleur moyen que j'ai trouvé pour y parvenir est d'utiliser une fonction récursive.

Vous pouvez auto-joindre tous les nœuds et vérifier s'ils sont enfants en utilisant la fonction GetAncestor

CREATE FUNCTION dbo.GetJson (ID hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
    RETURN (
        SELECT
          t.Id,
          t.HierarchyId,
          t.Name,
          children = JSON_QUERY(dbo.GetJson(t.HierarchyId))
        FROM Tree t
        WHERE t.HierarchyId <> ID
          AND t.HierarchyId.GetAncestor(1) = ID
        FOR JSON PATH
    );
END;

go

SELECT dbo.GetJson(hierarchyid::GetRoot());

db<>fidèle

Résultat

[
  {
    "Id": 1,
    "HierarchyId": "/1/",
    "Name": "MHz",
    "children": [
      {
        "Id": 2,
        "HierarchyId": "/1/2/",
        "Name": "Finance",
        "children": [
          {
            "Id": 6,
            "HierarchyId": "/1/2/6/",
            "Name": "Accountant"
          }
        ]
      },
      {
        "Id": 3,
        "HierarchyId": "/1/3/",
        "Name": "IT",
        "children": [
          {
            "Id": 4,
            "HierarchyId": "/1/3/4/",
            "Name": "Software Development"
          },
          {
            "Id": 5,
            "HierarchyId": "/1/3/5/",
            "Name": "QA"
          }
        ]
      }
    ]
  }
]