jsonsql-serversql-server-2016json-value

The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal


I am passing this JSON to a stored procedure in SQL Server

{
    "individual": [
        {
            "app_id": 1057029,
            "size": 2
        },
        {
            "app_id": 1057053,
            "size": 3
        },
        {
            "app_id": 1057048,
            "size": 1
        }
    ]
}

In the stored procedure I am extracting values of app_id and size as under

SET @len = JSON_VALUE(@json, CONCAT('$.individual[', @i, '].size'));
SET @appId = JSON_VALUE(@json, CONCAT('$.individual[', @i, '].app_id'));

(Here i is index variable incrementing in a loop)

This works perfect on Microsoft SQL Server 2017 (version 14.0.1000.169)

But on Microsoft SQL Server 2016 (version 13.0.4604.0) I am getting error:

JSON_Value error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

Please note this is not duplicate as I already have referred questions below on SO but still didn't get solution.

JSON_Value error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

SQL Sever 2016 - Inconsistent Behavior - The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal

Update Why this is not duplicate question?

None of the other questions discusses the issue in clear precise way. They have mentioned specifics instead. Rather in this question, I have mentioned use of variable @i which is precisely causing this error. JSON_VALUE works in MSSQL2016 but it doesn't support variables as second param, that precisely is the problem here. As a workaround, we've to use OPENJSON but again writing OPENJSON query to get record of particular index from json is tricky. None of the answers to other similar questions discusses this clearly. I am going to write an answer in some time demonstrating it all.


Solution

  • This example demonstrates the use of OpenJson for your JSON and desired query. I've never used OpenJson but found the Microsoft documentation more than adequate. In the last SELECT in this example, the app_id and size are columns in a table with each value pair as a row. Now you don't have to loop through an array; you now have a standard table to work with.

    DECLARE @json nvarchar(max) = '{
        "individual": [
            {
                "app_id": 1057029,
                "size": 2
            },
            {
                "app_id": 1057053,
                "size": 3
            },
            {
                "app_id": 1057048,
                "size": 1
            }
        ]
    }';
    
    SELECT * FROM OpenJson(@json);
    SELECT * FROM OpenJson(@json, '$.individual');
    
    SELECT * FROM OPENJSON(@json, '$.individual')
    WITH (  
        app_id int,
        size int
    ) as apps
    ;
    

    The output:

    enter image description here