SQL Server key column + JSON FOR PATH [duplicate]

6l7fqoea 于 11个月前发布在 SQL Server

关注(0)|答案(1)|浏览(114)

This question already has answers here:

How do you condense the values in a T-SQL pivot into a JSON? (2 answers)
Closed 29 days ago.

I would like to have a JSON with all columns excluding one which I would like to use as a key column to join with other tables. The result should be grouped by the key column.

SELECT
    GroupKey, -- Not in JSON
    Data_A, -- Regular JSON as with FOR JSON PATH
    Data_B, 
    Data_C, 
    ...
FROM 
    T
GROUP BY GroupKey
FOR JSON PATH

GroupKey	JSON
1	"[{...}]"

sql-server

来源：https://stackoverflow.com/questions/77613452/sql-server-key-column-json-for-path

1条答案

按热度按时间

b91juud31#

If you have a lot of columns and feeling lazy, you could do the following:

select  object_id
,   json_modify((select so.* for json path), '$[0].object_id', null)
from    sys.objects so

The idea is to create a json of all columns and then remove the id column by using JSON_MODIFY. Note that this will have slightly worse performance than above solutions, but saves you some copy pasting

赞(0）回复(0）举报 11个月前

我来回答

SQL Server key column + JSON FOR PATH [duplicate]

1条答案

相关问题

热门标签

最新问答