Accessing an array object from JSON by index-variable

Question

I want to access over an array object in JSON by index with a variable. Consider the following code:

declare @pjson nvarchar(max)='{
    "store":{
        "storeId": 100,
        "name": "TEST",
        "lastUpdatedBy": "MULE",
        "location": {
        declare @pjson nvarchar(max)='{
    "store":{
        "storeId": 100,
        "name": "TEST",
        "lastUpdatedBy": "MULE",
        "location": {
            "addresses": [
                {
                    "addressType": "MAIN",
                    "name": "Name1",
                    "name2": "Name2",
                    "address": "Address1",
                    "address2": "Address2",
                    "city": "City",
                    "lastUpdateBy": "MULE"
                },
                {
                    "addressType": "SECONDARY",
                    "name": "Name1",
                    "name2": "Name2",
                    "address": "Address1",
                    "address2": "Address2",
                    "city": "City",
                    "lastUpdateBy": "MULE"
                },
                {
                    "addressType": "BILLING",
                    "name": "Name1",
                    "name2": "Name2",
                    "address": "Address1",
                    "address2": "Address2",
                    "city": "City",
                    "lastUpdateBy": "MULE"
                }
            ]
        }
    }
}'

Declare @counter1 INT = 0;
Print JSON_VALUE(@pjson,N'lax $.store.location.addresses[@counter1].addressType')

I get an error:

JSON path is not properly formatted. Unexpected character '@' is found at position 31.

If I try directly by passing number as

Declare @counter1 INT = 0;
Print JSON_VALUE(@pjson,N'lax $.store.location.addresses[0].addressType')

I get the expected result

MAIN

Is there something that I am missing while passing the variable?

Answer 1

Try following:

DECLARE @pJson NVARCHAR(4000)
-- ...
DECLARE @Counter INT = 0
DECLARE @PathString NVARCHAR(1000)

SET @PathString = N'lax $.store.location.addresses[' + CAST(@Counter AS NVARCHAR(50)) + N'].addressType'

PRINT JSON_VALUE(@Pjson,@PathString)

Answer 2

I don't think that you can use a T-SQL variable directly as part of the path parameter in the JSON_VALUE() call, but you may try one of the following approaches:

• Concatenate the @counter variable in the path parameter (SQL Server 2017 is needed).
• Parse the JSON with OPENJSON() and the appropriate WHERE clause.

JSON:

DECLARE @counter INT = 0;
DECLARE @pjson nvarchar(max) = N'{
   "store":{
      "storeId":100,
      "name":"TEST",
      "lastUpdatedBy":"MULE",
      "location":{
         "addresses":[
            {
               "addressType":"MAIN",
               "name":"Name1",
               "name2":"Name2",
               "address":"Address1",
               "address2":"Address2",
               "city":"City",
               "lastUpdateBy":"MULE"
            },
            {
               "addressType":"SECONDARY",
               "name":"Name1",
               "name2":"Name2",
               "address":"Address1",
               "address2":"Address2",
               "city":"City",
               "lastUpdateBy":"MULE"
            },
            {
               "addressType":"BILLING",
               "name":"Name1",
               "name2":"Name2",
               "address":"Address1",
               "address2":"Address2",
               "city":"City",
               "lastUpdateBy":"MULE"
            }
         ]
      }
   }
}'

Statement with variable concatenation:

SELECT JSON_VALUE(
   @pjson, 
   CONCAT(N'lax $.store.location.addresses[', @counter, N'].addressType')
)

Statement with OPENJSON():

SELECT JSON_VALUE([value], '$.addressType')
FROM OPENJSON(@pjson, 'lax $.store.location.addresses')
WHERE CONVERT(int, [key]) = @counter

Result:

(No column name)
----------------
MAIN