Store a dynamic list of images in 1 row

Question

I am joining these two tables and I would like to send a view of the Test with its image(s).

• Test

  • Name (VARCHAR(255))
  • TestId (INT)
  • StartTime (DATETIME2(7))
  • Status (VARCHAR(255))

• Images

  • ImageId (INT)
  • TestId (INT)
  • Image (VARBINARY(MAX))

Right now I am doing this:

SELECT Name, t.TestId, StartTime, Status, Image
FROM [Test] t
LEFT JOIN [Images] i ON i.TestId = t.TestId

What I don't like here is that I have multiple rows from the same test but with different images. Is there a convenient way to return only one row of each test with its image(s) ?

My images are not that heavy (max 100KB) and I don't have a lot of images per test (max 10). Here's what I think:

1. It might sound crazy but maybe within SQL server there is a way to convert the Images into a list of images as a VARBINARY(MAX)
2. I could have 10 columns (image1, image2...) in the view and fill them with the images if they exist (NOT NULL)

I am a bit stuck with these solutions as I don't know a lot about SQL, but I am still trying. Do you see another way to do it? If not, could you help me with useful advice on how to achieve 1. or 2.

Answer 1

What I don't like here is that I have multiple rows from the same test but with different images.

So what? Its just a few bytes repeated for each row, which is insignificant compared to a 100KB blob.

But you can retrieve the data in exactly the shape you want, if you have SQL Server convert it to JSON. varbinary(max) will be base64 encoded and adding all the JSON overhead this will be a bigger result size. But a query like

SELECT Name , 
       t.TestId , 
       StartTime , 
       Status ,  
       (select Image from Images where testid = t.testid for json path) Images
FROM [Test] t
for json path

will output data like

[
    {
        "Name": "Test1",
        "TestId": 1,
        "StartTime": "2020-04-22T18:15:47.9533333",
        "Status": "complete",
        "Images": [
            {
                "Image": "j2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKqPYtg/L1TLRZvPT+qw+TSqj2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKo="
            },
            {
                "Image": "j2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKqPYtg/L1TLRZvPT+qw+TSqj2LYPy9Uy0Wbz0/qsPk0qo9i2D8vVMtFm89P6rD5NKo="
            }
        ]
    }
]