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?

You need at least SQL Server 2017 to use a variable as part of the path parameter. I don't think that you can use @counter directly as part of the path parameter. And declare @pjson nvarchar(max)=' part of the test JSON is probably a typing error. — Zhorov
– Zhorov, Commented Mar 30, 2021 at 7:22

Zhorov · Accepted Answer · 2021-03-30 07:34:38Z

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

Dharman · Accepted Answer · 2021-03-30 07:46:27Z

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)

edited Mar 30, 2021 at 7:46

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Mar 30, 2021 at 7:41

droebi

9441 gold badge17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Accessing an array object from JSON by index-variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related