Checking the JSON file information without using a temp table in T-SQL-CodePudding

Let's say I have a JSON input parameter called @ImageURL in my procedure with the following structure which is used for storing and manipulating my Uploader section.

    [
        {
          "Title": "some title",
          "Urls": "some url",
          "Type": "application/pdf",
          "IsPrimary": true
        },
        ...
    ]

This JSON string always has more than one set of Title, URLs and etc. but there are some cases in which I insist to check that it contains only one since I can't be sure if front-end developers will send the JSON for me in a right format. For checking this, I have used the following code which seems to be working but I feel like it's unclean and there might be a better way to write this than creating a TMP table and checking it's COUNT.

    DECLARE @JSONImageFile TABLE(
        Level1ID INT,
        JSONImageFile NVARCHAR(MAX),
        JSONImageFileTitle NVARCHAR(1000),
        JSONImageFileType NVARCHAR(100),
        JSONImageFileIsPrimary BIT
    )
    
    INSERT INTO @JSONImageFile(
        Level1ID,
        JSONImageFile,
        JSONImageFileTitle,
        JSONImageFileType,
        JSONImageFileIsPrimary
    )
    SELECT
        @Level1ID,
        Urls,
        Title,
        Type,
        IsPrimary
    FROM OPENJSON(@ImageURL) WITH (
        Urls        NVARCHAR(MAX),
        Title       NVARCHAR(1000),
        Type        NVARCHAR(100),
        IsPrimary   BIT
    )

    IF (SELECT COUNT(*) FROM @JSONImageFile) = 1 /* the main code */

Thanks in advance for your answers.

CodePudding user response：

In case of a JSON array, you may try to parse this array with OPENSJON() and default schema and check the max value of the returned key column:

JSON:

DECLARE @ImageUrl nvarchar(max) = 
N'[
        {
          "Title": "some title",
          "Urls": "some url",
          "Type": "application/pdf",
          "IsPrimary": true
        },
        {
          "Title": "some title",
          "Urls": "some url",
          "Type": "application/pdf",
          "IsPrimary": true
        }
]'

Statement:

IF EXISTS (
   SELECT 1 
   FROM OPENJSON(@ImageUrl) 
   HAVING MAX(CONVERT(int, [key])) = 0
) 
   PRINT 'Success'
ELSE 
   PRINT 'Error'