How can I use a dot/period in a column name with FOR JSON PATH without it creating a nested object?

Question

Given the following script:

DECLARE @table1 TABLE (t1num int NOT NULL);
DECLARE @table2 TABLE (t2num int NOT NULL);
DECLARE @table3 TABLE (t3num int NOT NULL);

INSERT INTO @table1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO @table2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO @table3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);


SELECT
    t2num,
    (
        SELECT t1num AS [t1.num]
        FROM @table1
        FOR JSON PATH
    ) AS t1s
FROM @table2 [t2]

The output for the t1s column will look like this:

[{"t1":{"num":1}},{"t1":{"num":2}},{"t1":{"num":3}},{"t1":{"num":4}},{"t1":{"num":5}},{"t1":{"num":6}},{"t1":{"num":7}},{"t1":{"num":8}},{"t1":{"num":9}},{"t1":{"num":10}}]

However, I need it to look like this:

[{"t1_num":1},{"t1_num":2},{"t1_num":3},{"t1_num":4},{"t1_num":5},{"t1_num":6},{"t1_num":7},{"t1_num":8},{"t1_num":9},{"t1_num":10}]

...except with periods instead of underscores.

If I try to double-dot, SQL Server returns this error:

Msg 13603, Level 16, State 1, Line 10
Property 't1..num' cannot be generated in JSON output due to invalid character in the column name or alias. Column name or alias that contains '..', starts or ends with '.' is not allowed in query that has FOR JSON clause.

And if I try to escape the dot with a backslash, the backslash is taken as a literal character and the object is still nested:

[{"t1\\":{"num":1}},{"t1\\":{"num":2}},{"t1\\":{"num":3}},{"t1\\":{"num":4}},{"t1\\":{"num":5}},{"t1\\":{"num":6}},{"t1\\":{"num":7}},{"t1\\":{"num":8}},{"t1\\":{"num":9}},{"t1\\":{"num":10}}]

How can I accomplish what I want?

Answer 1

It's not elegent, but this is where my thinking takes me... I just added a replace(...,'_','.')

DECLARE @table1 TABLE (t1num int NOT NULL);
DECLARE @table2 TABLE (t2num int NOT NULL);
DECLARE @table3 TABLE (t3num int NOT NULL);

INSERT INTO @table1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO @table2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO @table3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);


SELECT
    t2num,
    replace((
        SELECT t1num AS [t1_num]
        FROM @table1
        FOR JSON PATH
    ),'_','.') AS t1s
FROM @table2 [t2]

Returns

t2num   t1s
1       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
2       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
3       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
4       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
5       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
6       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
7       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
8       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
9       [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]
10      [{"t1.num":1},{"t1.num":2},{"t1.num":3},{"t1.num":4},{"t1.num":5},{"t1.num":6},{"t1.num":7},{"t1.num":8},{"t1.num":9},{"t1.num":10}]

dbFiddle