Q:

Select as JSON joined tables

I would like to select data from 2 tables into one json string. First table should list all linked tables from second table like
First table:
SELECT [orderNumber], [tcpState] 
FROM [Tracking]

Second table:
SELECT [startdate], [enddate], [tcpState], [orderNumber], [name]
FROM [Stations]

Tracking can link several stations.
Expected result:
[{
        "orderNumber": 123455,
        "tcpState": 3,
        "Stations": [{
                "startdate": "2011-05-06",
                "enddate": "2012-09-15",
                "tcpState": 3,
                "name": "Roger"
            },
            {
                "startdate": "2011-02-06",
                "enddate": "2012-05-15",
                "tcpState": 4,
                "name": "Hans"
            }
        ]
    },
    {
        "orderNumber": 1566,
        "tcpState": 3,
        "Stations": [{
                "startdate": "2011-06-06",
                "enddate": "2012-08-15",
                "tcpState": "6",
                "name": "Mike"
            },
            {
                "startdate": "2011-03-06",
                "enddate": "2012-03-15",
                "tcpState": "6",
                "name": "Tom"
            }
        ]
    }
]

A:

Ok, first you forced me to install sql-server 2017 on my linux just to test the query as there is no online tester available, so I hate you a little, but thanks, Docker was there...

There is two way to do it :
The easy one, you found after the hard one
Using FOR JSON AUTO
SELECT 
    [Tracking].[orderNumber], 
    [Tracking].[tcpState],  
    [Stations].[startdate],
    [Stations].[enddate],
    [Stations].[tcpState],
    [Stations].[name]
FROM [Tracking]
LEFT JOIN [Stations] 
    ON [Tracking].[orderNumber] = [Stations].[orderNumber]
FOR JSON AUTO

The hard one, crafting you JSON by yourself
/!\ this use the STRING_AGG function, SQL-server 2017 and after only /!\
SELECT 
    '['+iif(STRING_AGG(t1.[orderNumber],',') IS NOT NULL, 
        STRING_AGG(
        '{'+
        '"orderNumber":'+CONVERT(varchar(10), t1.[orderNumber])+','+
        '"tcpState":'+CONVERT(varchar(10), t1.[tcpState])+','+
        '"Stations":'+t1.Stations+
        '}'
        , ','), '')+
    ']' json
FROM
(
    SELECT 
    t.[orderNumber], 
    t.[tcpState],  
    '['+ iif(STRING_AGG(s.[orderNumber],',') IS NOT NULL, 
        STRING_AGG(
        '{'+
        '"startdate":"'+s.[startdate]+'",'+
        '"enddate":"'+s.[enddate]+'",'+
        '"tcpState":'+CONVERT(varchar(10), s.[tcpState])+','+
        '"name":"'+s.[name]+'"'+
        '}'
        , ','), '')+']' Stations
    FROM [Tracking] t
    LEFT JOIN [Stations] s ON t.[orderNumber] = s.[orderNumber]
    GROUP BY t.[orderNumber], t.[tcpState]
) t1
GROUP BY t1.[orderNumber]

