将4个列合并为一个新列，作为单个JSON对象 (SQL)

Question

编辑：FYI。所以我的PrimaryImage实际上是一个int，并且被用作我的Images表的外键。我只是想解释一下，这样清楚为什么我将它设置为on f.PrimaryImage = i.Id。由于某种原因，所有的行都会填充每个i.Id、i.EntityId、i.ImageTypeId和i.ImageUrl，而不仅仅是我的f.PrimaryImage = i.Id。

我正在编写一个SQL存储过程来选择所有内容，我想将我的最后4列 Id，EntityId，ImageTypeId，ImageUrl 合并为一个新列 PrimaryImage 作为单个JSON对象。我已经成功地对我的Skills列进行了操作，但是为此我需要它作为包含JSON对象的数组，因此只需使用 "FOR JSON AUTO" 就可以解决这个问题。但是像我所说的那样，对于 PrimaryImage，我需要将其作为包含我的 Id，EntityId，ImageTypeId 和 ImageUrl 的单个JSON对象。我在执行此过程后包含了我的结果图片，并在我的表格正下方画了我希望该列看起来像什么的表示。为了澄清，我有四个表，我的Friends、FriendSkills、Skills和Images表，我已经使用连接语句进行了组织。基本上，我的模式应该看起来像这样：

{
"id": 0000,
"userId": "String"
"bio": "String",
"title": "String",
"summary": "String",
"headline": "String",
"statusId": "String",
"slug": "String",
"skills": [{id: 0, name: "String"},{id: 0, name: "String"}],
"primaryImage": {
          "id": 0,
          "entityId": 0,
          "imageTypeId": 0,
          "imageUrl": "String"
        }
}

这是我的存储过程。

ALTER PROC [dbo].[Friends_SelectAllV2]


AS

/* --- Test Proc ------

Execute dbo.Friends_SelectAllV2

*/

BEGIN

    Select  f.Id
            ,f.UserId
            ,f.DateAdded
            ,f.DateModified
            ,f.Title
            ,f.Bio
            ,f.Summary
            ,f.Headline
            ,f.Slug
            ,f.StatusId
            ,Skills = ( SELECT s.Id,
                        s.Name
                        From dbo.Skills as s inner join dbo.FriendSkills fs
                        on s.Id = fs.SkillId
                        Where  f.Id = fs.FriendId
                        FOR JSON AUTO
            ),
            PrimaryImage = (SELECT i.Id,
                            i.EntityId,
                            i.ImageTypeId,
                            i.ImageUrl
                            From dbo.Friends f left join dbo.Images as i
                            on f.PrimaryImage = i.Id
                            Where f.PrimaryImage = i.Id
                            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
            
    
END

- Shawn Fetanat

---

你可以对新的 primaryImage 列执行相同的操作。 - Zhorov

基本上与“技能”列相同，只需添加without_array_wrapper。 - AlwaysLearning

@ShawnFetanat，方法是一样的（类似于primaryImage = (SELECT i.Id、i.EntityId、i.ImageTypeId、i.ImageUrl FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)），我只是错过了WITHOUT_ARRAY_WRAPPER的解释。但可能存在一个问题，那就是URL的转义。我将其发布为评论，因为stackoverflow.com上有许多关于同样问题的问题。谢谢。 - Zhorov

记录一下，这里没有任何有用的答案。@Zhorov可能忘了提到，使用WITHOUT_ARRAY_WRAPPER不会生成有效的JSON文本，除非只有一行数据。这可能就是为什么我在每一行中都得到所有的JSON对象，而不仅仅是f.PrimaryImage = i.Id的地方。 - Shawn Fetanat

Answer 1

如果内部属性是单个对象，则实际上不需要另一个子查询，您可以使用显式路径语法的FOR JSON PATH。

Select       f.Id
            ,f.UserId
            ,f.DateAdded
            ,f.DateModified
            ,f.Title
            ,f.Bio
            ,f.Summary
            ,f.Headline
            ,f.Slug
            ,f.StatusId
            ,Skills = ( SELECT s.Id,
                        s.Name
                        From dbo.Skills as s inner join dbo.FriendSkills fs
                        on s.Id = fs.SkillId
                        Where  f.Id = fs.FriendId
                        FOR JSON AUTO
            ),
            i.Id AS [PrimaryImage.Id],
            i.EntityId AS [PrimaryImage.EntityId],
            i.ImageTypeId AS [PrimaryImage.ImageTypeId],
            i.ImageUrl AS [PrimaryImage.ImageUrl]
From dbo.Friends f
left join dbo.Images as i on f.PrimaryImage = i.Id
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

如果有多个图片，而且你需要一个子查询，问题在于你再次放入了整个 Friends 表，而不是关联外部表。你还需要删除 WITHOUT_ARRAY_WRAPPER。

    Select  f.Id
            ,f.UserId
            ,f.DateAdded
            ,f.DateModified
            ,f.Title
            ,f.Bio
            ,f.Summary
            ,f.Headline
            ,f.Slug
            ,f.StatusId
            ,Skills = ( SELECT s.Id,
                        s.Name
                        From dbo.Skills as s inner join dbo.FriendSkills fs
                        on s.Id = fs.SkillId
                        Where  f.Id = fs.FriendId
                        FOR JSON AUTO
            ),
            PrimaryImage = (SELECT i.Id,
                            i.EntityId,
                            i.ImageTypeId,
                            i.ImageUrl
                            FROM dbo.Images as i
                            WHERE f.PrimaryImage = i.Id
                            FOR JSON PATH
            )
    From dbo.Friends f
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

- Charlieface