Generate nested nth level JSON SQL Server using recursive CTE

Question

I have data on the following structure

I want to generate nested JSON using parent-property id relation.

Desired output.

[{
    "propertyID": 1
    , "title": "foo"
    , "class": ""
    , "typeid": 150
    , "value": "bar"
    , "children": [{}]
}, {
    "propertyID": 2
    , "title": "foo"
    , "class": ""
    , "typeid": 128
    , "value": "bar"
    , "children": [{}]
}, {
    "propertyID": 3
    , "title": "foo"
    , "class": ""
    , "typeid": 128
    , "value": "bar"
    , "children": [{
        "propertyID": 4
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{
                     "propertyID": 41
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{
                     "propertyID": 411
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{}]
                  }]
                  },{
                     "propertyID": 42
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{
                     "propertyID": 421
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{}]
                  }]
                  }]
    }, {
        "propertyID": 5
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{}]
    }, {
        "propertyID": 6
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{
            "propertyID": 7
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{
                "propertyID": 8
                , "title": "foo"
                , "class": ""
                , "typeid": 128
                , "value": "bar"
                , "children": [{
                   "propertyID": 9
                  , "title": "foo"
                  , "class": ""
                  , "typeid": 128
                  , "value": "bar"
                  , "children": [{
                     "propertyID": 10
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{}]
                  }]
                }]
            }]
        }]
    }]
}]

I found a similar question here. But I want to do this without function. Although there are few similar questions here nothing fits into my requirement. So far I tried this. but it doesn't work when the same node with two children. it duplicates the result.

This is what I tried.

;WITH childrens AS (
 SELECT propertyID, parentID, title, children
 FROM CTE WHERE children = '[{}]'

 union ALL

 SELECT cte.propertyID, cte.parentID, cte.title, JSON_QUERY(
   (SELECT c.propertyID, c.parentID, c.title, JSON_QUERY(c.children) AS children FOR JSON 
 PATH)
 ) AS children
 FROM CTE
 INNER JOIN childrens c on CTE.propertyID = c.parentID
 ), Tree as (
 SELECT c.propertyID, c.parentID, c.title, JSON_QUERY(
  (
    SELECT ch.propertyID, ch.parentID, ch.title, JSON_QUERY(ch.children) AS children
    FROM childrens ch
    WHERE c.propertyID = ch.parentID FOR JSON PATH
  )
) AS children
FROM childrens c WHERE parentID = 0
)
select DISTINCT *
from tree FOR JSON PATH

What I got is

    [
   {
      "propertyID":5,
      "parentID":3,
      "title":"foo",
      "children":[
         {
            
         }
      ]
   },
   {
      "propertyID":4,
      "parentID":3,
      "title":"foo",
      "children":[
         {
            "propertyID":41,
            "parentID":4,
            "title":"foo",
            "children":[
               {
                  "propertyID":411,
                  "parentID":41,
                  "title":"foo",
                  "children":[
                     {
                        
                     }
                  ]
               }
            ]
         }
      ]
   },
   {
      "propertyID":4,
      "parentID":3,
      "title":"foo",
      "children":[
         {
            "propertyID":42,
            "parentID":4,
            "title":"foo",
            "children":[
               {
                  "propertyID":421,
                  "parentID":42,
                  "title":"foo",
                  "children":[
                     {
                        
                     }
                  ]
               }
            ]
         }
      ]
   },
   {
      "propertyID":6,
      "parentID":3,
      "title":"foo",
      "children":[
         {
            "propertyID":7,
            "parentID":6,
            "title":"foo",
            "children":[
               {
                  "propertyID":8,
                  "parentID":7,
                  "title":"foo",
                  "children":[
                     {
                        "propertyID":9,
                        "parentID":8,
                        "title":"foo",
                        "children":[
                           {
                              "propertyID":10,
                              "parentID":9,
                              "title":"foo",
                              "children":[
                                 {
                                    
                                 }
                              ]
                           }
                        ]
                     }
                  ]
               }
            ]
         }
      ]
   }
]

It duplicates node with property = 4. any suggestions to do this.

sql fiddle sample is here

Charlieface · Accepted Answer · 2025-02-25 13:37:57Z

Having gone over this a number of times in a number of different ways, it seems to me that the issue is that SQL Server is not able to use aggregation within a recursive CTE, so you cannot recursively aggregate all children of each row.

The easiest way I've found to do this is (horror of horrors!) a scalar User Defined Function (an inline TVF cannot be recursed).

db<>fiddle

CREATE FUNCTION dbo.GetJson (@parentID int)
RETURNS nvarchar(max)
AS BEGIN
    RETURN (
        SELECT
          propertyID,
          title,
          typeid,
          [value],
          children = JSON_QUERY(dbo.GetJson(propertyID))
        FROM property p
        WHERE p.parentID IS NOT DISTINCT FROM @parentID
        FOR JSON PATH
    );
END;

SELECT dbo.GetJson(NULL);

Result

[
  {
    "propertyID": 1,
    "title": "foo",
    "typeid": 150,
    "value": "bar"
  },
  {
    "propertyID": 2,
    "title": "foo",
    "typeid": 150,
    "value": "bar"
  },
  {
    "propertyID": 3,
    "title": "foo",
    "typeid": 150,
    "value": "bar",
    "children": [
      {
        "propertyID": 4,
        "title": "foo",
        "typeid": 150,
        "value": "bar",
        "children": [
          {
            "propertyID": 41,
            "title": "foo",
            "typeid": 150,
            "value": "bar",
            "children": [
              {
                "propertyID": 411,
                "title": "foo",
                "typeid": 150,
                "value": "bar"
              }
            ]
          },
          {
            "propertyID": 42,
            "title": "foo",
            "typeid": 150,
            "value": "bar",
            "children": [
              {
                "propertyID": 421,
                "title": "foo",
                "typeid": 150,
                "value": "bar"
              }
            ]
          }
        ]
      },
      {
        "propertyID": 5,
        "title": "foo",
        "typeid": 150,
        "value": "bar"
      },
      {
        "propertyID": 6,
        "title": "foo",
        "typeid": 150,
        "value": "bar",
        "children": [
          {
            "propertyID": 7,
            "title": "foo",
            "typeid": 150,
            "value": "bar",
            "children": [
              {
                "propertyID": 8,
                "title": "foo",
                "typeid": 150,
                "value": "bar",
                "children": [
                  {
                    "propertyID": 9,
                    "title": "foo",
                    "typeid": 150,
                    "value": "bar",
                    "children": [
                      {
                        "propertyID": 10,
                        "title": "foo",
                        "typeid": 150,
                        "value": "bar"
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]

Paul White · Accepted Answer · 2022-07-24 17:21:37Z

This is a recursive solution. It constructs per-row json objects in path order, adding 'children' elements as it goes, and closing arrays and objects when necessary. The final result is a concatenation of the individual objects, wrapped as an array:

WITH 
    Recursion AS
    (
        -- Anchor part
        SELECT 
            P.propertyID, 
            depth = 1,
            rpath = CONVERT(nvarchar(4000), P.propertyID),
            has_children = 
                IIF
                (
                    EXISTS
                    (
                        SELECT C.* 
                        FROM dbo.property AS C 
                        WHERE C.parentId = P.propertyID
                    ), 
                    1, 0
                ),
            element = 
                (
                    SELECT P.propertyID, P.title, P.typeid, P.[value] 
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                )
        FROM dbo.property AS P
        WHERE P.parentID = 0

        UNION ALL 

        -- Recursive part
        SELECT
            P.propertyID,
            depth = R.depth + 1,
            rpath = CONCAT_WS(N'.', R.rpath, P.propertyID),
            has_children = 
                IIF
                (
                    EXISTS
                    (
                        SELECT C.* 
                        FROM dbo.property AS C 
                        WHERE C.parentId = P.propertyID
                    ), 
                    1, 0
                ),
            element = 
                (
                    SELECT P.propertyID, P.title, P.typeid, P.[value] 
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                )
        FROM Recursion AS R
        JOIN dbo.property AS P
            WITH (FORCESEEK)
            ON P.parentID = R.propertyID
    ),
    AddNextDepth AS
    (
        SELECT
            R.*,
            NextDepth = 
                LEAD(R.depth) OVER (
                    ORDER BY R.rpath)
        FROM Recursion AS R
    ),
    ModifiedTree AS
    (
        SELECT 
            ND.rpath, 
            element =
                CONCAT
                (
                    -- Insert "children" element
                    IIF
                    (
                        -- No children, add empty array
                        ND.has_children = 0,
                        STUFF
                        (
                            ND.element,
                            LEN(ND.element),
                            0,
                            N',"children":[{}]'
                        ),
                        -- Insert "children" element
                        STUFF
                        (
                            ND.element,
                            LEN(ND.element),
                            1,
                            N',"children":['
                        )
                    ),
                    -- Close previously opened array(s) if necessary
                    REPLICATE
                    (
                        N']}', 
                        -- Number of closures needed
                        ND.depth - ISNULL(ND.NextDepth, 1)
                    ),
                    -- Add comma if no children and not the last line
                    IIF
                    (
                        ND.has_children = 0 
                            AND ND.NextDepth IS NOT NULL, 
                        N',', 
                        N''
                    )
                )
        FROM AddNextDepth AS ND
    )
-- Concatenate objects in path order and add array wrapper
SELECT
    CONCAT
    (
        N'[',
        STRING_AGG(MT.element, N'') 
            WITHIN GROUP (ORDER BY MT.rpath),
        N']'
    )
FROM ModifiedTree AS MT;

Output:

[
  {
    "propertyID": 1,
    "title": "foo",
    "typeid": 150,
    "value": "bar",
    "children": [
      {}
    ]
  },
  {
    "propertyID": 2,
    "title": "foo",
    "typeid": 128,
    "value": "bar",
    "children": [
      {}
    ]
  },
  {
    "propertyID": 3,
    "title": "foo",
    "typeid": 128,
    "value": "bar",
    "children": [
      {
        "propertyID": 4,
        "title": "foo",
        "typeid": 128,
        "value": "bar",
        "children": [
          {
            "propertyID": 41,
            "title": "foo",
            "typeid": 128,
            "value": "bar",
            "children": [
              {
                "propertyID": 411,
                "title": "foo",
                "typeid": 128,
                "value": "bar",
                "children": [
                  {}
                ]
              }
            ]
          },
          {
            "propertyID": 42,
            "title": "foo",
            "typeid": 128,
            "value": "bar",
            "children": [
              {
                "propertyID": 421,
                "title": "foo",
                "typeid": 128,
                "value": "bar",
                "children": [
                  {}
                ]
              }
            ]
          }
        ]
      },
      {
        "propertyID": 5,
        "title": "foo",
        "typeid": 128,
        "value": "bar",
        "children": [
          {}
        ]
      },
      {
        "propertyID": 6,
        "title": "foo",
        "typeid": 128,
        "value": "bar",
        "children": [
          {
            "propertyID": 7,
            "title": "foo",
            "typeid": 128,
            "value": "bar",
            "children": [
              {
                "propertyID": 8,
                "title": "foo",
                "typeid": 128,
                "value": "bar",
                "children": [
                  {
                    "propertyID": 9,
                    "title": "foo",
                    "typeid": 128,
                    "value": "bar",
                    "children": [
                      {
                        "propertyID": 10,
                        "title": "foo",
                        "typeid": 128,
                        "value": "bar",
                        "children": [
                          {}
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]

db<>fiddle

Collectives™ on Stack Overflow

Generate nested nth level JSON SQL Server using recursive CTE

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related