gpt4 book ai didi

sql-server - 如何在带有 FOR JSON PATH 的列名中使用点/句点而不创建嵌套对象?

转载 作者:行者123 更新时间:2023-12-03 16:52:49 27 4
gpt4 key购买 nike

鉴于以下脚本:

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]
t1s 的输出列将如下所示:
[{"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}}]
但是,我需要它看起来像这样:
[{"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}]
...除了句点而不是下划线。

如果我尝试双点,SQL Server 返回此错误:
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.

如果我尝试用反斜杠转义点,反斜杠将被视为文字字符并且对象仍然嵌套:
[{"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}}]
我怎样才能完成我想要的?

最佳答案

它并不优雅,但这是我思考的地方...我刚刚添加了一个 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]

返回
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

关于sql-server - 如何在带有 FOR JSON PATH 的列名中使用点/句点而不创建嵌套对象?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43870687/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com