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

Question

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.

It is [documented](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver16) that this won't work on 2016: *"In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path."* Why not switch to `OPENJSON` like the linked questions suggest? You say this isn't a duplicate, but the second link appears to be exactly the answer you want/need. If it isn't a duplicate, explain why. — Thom A, Nov 16 '22 at 18:57
@Larnu Thanks for comment but not sure how to do using `OPENJSON` Can you please give brief answer? Would be really great. — Atul, Nov 16 '22 at 19:00
@Larnu Updated the question mentioning why this is not duplicate. — Atul, Nov 18 '22 at 08:41

score 5 · Accepted Answer · answered Nov 16 '22 at 19:59

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:

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

1 Answers1