Generate nested nth level JSON SQL Server using recursive CTE

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 EXISTS (SELECT parentID INTERSECT SELECT @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"
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
]