How to access a JSON file with one nested array in SQL Server and insert that nested array into a Table?

Question

So, my question is basically this.

I have this stored procedure that uses a JSON file to insert or update a table SERVICES using MERGE.

The thing is, this JSON file has a nested array, which I must use to fill a second table SERVICE_IMAGES using the first table's IdService as a foreign key.

DECLARE @Services NVARCHAR(MAX) = N'[
    {
      "IdService": 10,
      "Name": "Nails",
      "Images_services": [
          {
            "Image_Id": "aaaaaa",          //This is unique ID
            "IdService": 10,              //This IdService should be the same as above
            "Image_Base64": "aaaaaa"     //This is a base64 Image 
          },          
          {
            "Image_Id": "bbbbb",     
            "IdService": 10,              
            "Image_Base64": "bbbbb"  
          },
          {
            "Image_Id": "ccccccccc",     
            "IdService": 10,              
            "Image_Base64": "ccccccccc"  
          }
      ]
   }
]
'

I already did the part where I insert or update the data into Services (IdService, Name) but I have no clue how to insert or update the Service_Images with the nested array Images_services.

For now I tried this but of course is not working:

SET @IdService = SCOPE_IDENTITY()
DECLARE @UNIQUEX UNIQUEIDENTIFIER
SET @UNIQUEX = NEWID();

MERGE SERVICIO_IMAGENES AS TARGET
USING(
    SELECT *
    FROM OPENJSON(@JsonService, '$.Images_Service')
    WITH (
        Image_Id varchar(36) '$.Images_Service."Image_Id"',
        IdService INT '$.Image_Service."IdService"',
        Image_Base64 varchar(36) '$.Images_Service."Image_Base64"'
    ) AS JsonService
) AS SOURCE(Image_Id, IdService, Image_Base64)
ON TARGET.ID_SERVICE = @IdService
WHEN MATCHED THEN
    UPDATE SET TARGET.IMAGE_BASE64 = SOURCE.Image_Base64, TARGET.ID_IMAGE = @UNIQUEX, TARGET.ID_SERVICE = @IdService
WHEN NOT MATCHED THEN
    INSERT (ID_IMAGE, ID_SERVICE, IMAGE_BASE64) VALUES (@UNIQUEX, @IdService, SOURCE.Image_Base64);

The end result should looks something like this:

SERVICES

| IdService | Name  |
+-----------+-------+
|    10     | Nails |

SERVICE_IMAGES:

| Image_Id | IdService | Image_Base64 |
+----------+-----------+--------------+
|  aaaaaa  |    10     |  aaaaaa      |
|  bbbbbb  |    10     |  bbbbbb      |
|  cccccc  |    10     |  cccccc      |

Answer 1

You need a different statement to parse the $.Images_services JSON array and this statement depends on the structure of the input JSON. For this specific case you need nested OPENJSON() calls with explicit schema (the WITH clause) and AS JSON option (to specify that the referenced property contains an inner JSON object or array). The following statements demonstrate this approach:

JSON:

DECLARE @Services NVARCHAR(MAX) = N'[
    {
      "IdService": 10,
      "Name": "Nails",
      "Images_services": [
          {
            "Image_Id": "aaaaaa",          
            "IdService": 10,              
            "Image_Base64": "aaaaaa"      
          },          
          {
            "Image_Id": "bbbbb",     
            "IdService": 10,              
            "Image_Base64": "bbbbb"  
          },
          {
            "Image_Id": "ccccccccc",     
            "IdService": 10,              
            "Image_Base64": "ccccccccc"  
          }
      ]
   }
]
'

Statement for services:

SELECT j1.IdService, j1.Name
FROM OPENJSON(@Services, '$') WITH (
   IdService int '$.IdService',
   Name varchar(100) '$.Name'
) j1

Statement for service images:

SELECT j2.*
FROM OPENJSON(@Services, '$') WITH (
   IdService int '$.IdService',
   Name varchar(100) '$.Name',
   Images_services nvarchar(max) '$.Images_services' AS JSON
) j1
CROSS APPLY OPENJSON(j1.Images_services)
WITH (
   Image_Id varchar(36) '$.Image_Id',
   IdService int '$.IdService',
   Image_Base64 varchar(36) '$.Image_Base64'
) j2

Results:

IdService   Name
10          Nails

Image_Id    IdService   Image_Base64
aaaaaa      10          aaaaaa
bbbbb       10          bbbbb
ccccccccc   10          ccccccccc