CODEWITHSUNDEEP
sqljsonsql-server
Shawn Fetanat
Shawn Fetanat

Reputation: 67

SQL - Combine 4 columns into one new column as a single JSON object

Edit: FYI. So my PrimaryImage is actually an int and is used as a foreign key to my Images table. I just wanted to explain that so it is clear why I set it to on f.PrimaryImage = i.Id. For some reason all my rows are getting populated with every i.Id, i.EntityId, i.ImageTypeId, & i.ImageUrl instead of just where my f.PrimaryImage = i.Id.

I am writing a SQL stored procedure to SELECT ALL and I want to combine my last 4 columns Id, EntityId, ImageTypeId, ImageUrl into one new column PrimaryImage as a single JSON object. I was able to successfully do that with my Skills column but for that I needed it as an array that holds JSON objects so simply using "FOR JSON AUTO" took care of that. But like I said for PrimaryImage, I need that as a single JSON object that contains my Id, EntityId, ImageTypeId, & ImageUrl. I included a picture of my result after executing this proc and right below my table I drew a representation of what I want the column to look like. Just to clarify I have four tables my Friends, FriendSkills, Skills, & Images tables which I've used join statements organize accordingly. Basically my schema needs to look like this:


{
"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"
        }
}

Here is my stored procedure


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

Upvotes: 4

Views: 5241

Answers (1)

Charlieface
Charlieface

Reputation: 71578

You don't actually need another subquery if the inner property is a single object, you can use FOR JSON PATH with explicit path syntax.

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;

If however there are multiple images, and you need a subquery, the issue here is that you are putting the whole Friends table in again, instead of correlating the outside table. You would also remove 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;

Upvotes: 3

Related Questions