I have the following sample data in a MS-SQL database:
(Microsoft SQL Server Standard Version 13; Microsoft SQL Server Management Studio 18)

+----------+-----------+-----+--------+---------+---------+
| LastName | Firstname | Age | Weight | Sallery | Married |
+----------+-----------+-----+--------+---------+---------+
| Smith    | Stan      |  58 |     87 |  59.000 | true    |
| Smith    | Maria     |  53 |     57 |  45.000 | true    |
| Brown    | Chris     |  48 |     77 | 159.000 | true    |
| Brown    | Stepahnie |  39 |     67 |  95.000 | true    |
| Brown    | Angela    |  12 |     37 |     0.0 | false   |
+----------+-----------+-----+--------+---------+---------+

I want to get a nested JSON array from it that looks like this:

 [
        {
            "Smith": [
                {
                    "Stan": [
                        {
                            "Age": 58,
                            "Weight": 87,
                            "Sallery": 59.000,
                            "Married": true
                        }
                    ],
                    "Maria": [
                        {
                            "Age": 53,
                            "Weight": 57,
                            "Sallery": 45.000,
                            "Married": true
                        }
                    ]
                }
            ],
            "Brown": [
                {
                    "Chris": [
                        {
                            "Age": 48,
                            "Weight": 77,
                            "Sallery": 159.000,
                            "Married": true
                        }
                    ],
                    "Stepahnie": [
                        {
                            "Age": 39,
                            "Weight": 67,
                            "Sallery": 95.000,
                            "Married": true
                        }
                    ],
                    "Angela": [
                        {
                            "Age": 12,
                            "Weight": 37,
                            "Sallery": 0.0,
                            "Married": false
                        }
                    ]
                }
            ]
        }
    ]

How do I have to build the SQL query?

I have tried different ways but I don’t get to dynamize the root or the root keeps repeating itself….

For example, I tried the following query:

SELECT
  LastName ,json  
FROM Table1 as a
OUTER APPLY (
  SELECT
    FirstName
  FROM Table1 as b
  WHERE a.LastName = b.LastName
  FOR JSON PATH
) child(json)
FOR JSON PATH