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      |

Can you edit your sample so that it is valid JSON. At the very least you need closing ] } ]. Then you have an array element which would usually be enclosed by something else — Nick.Mc
– Nick.Mc, Commented May 23, 2020 at 2:15

Zhorov · Accepted Answer · 2020-05-25 12:27:40Z

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

@HarryAgámez If this answer solves your problem, you may accept it.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related