Indexing and querying JSON array

Question

I have JSON values like this stored in a table:

{  
   "properties":[  
      {  
         "address":{  
            "value":"A3",
            "name":"",
            "prop":"",
            "path":[  
               "RealOptionsList9293"
            ],
            "type":"local"
         },
         "value":{  
            "type":11,
            "value":"portland"
         },
         "dependents":[  

         ],
         "save":true
      }
   ]
}

I'd like to index on address.value and value.value so that I can query on them. MSDN examples are for basic property using computed column. It does not cover indexing an array. Is indexing possible on array? Any example will be helpful.

I'd like to query for rows with:

JSON_VALUE(mycolumn, '$.properties[*].address.value') = 'A3'
AND JSON_VALUE(mycolumn, $.properties[*].value.value) = 'portland'

I don't see [*] syntax. Should I use OPENJSON() instead? If I use it, should I use a materialized view?

Zhorov · Accepted Answer · 2019-08-10 23:21:29Z

If you want to query a JSON array, OPENJSON() is more appropriate:

Table:

CREATE TABLE #Data (
   JsonData nvarchar(max)
)
INSERT INTo #Data (JsonData)
VALUES (N'{  
   "properties":[  
      {  
         "address":{  
            "value":"A3",
            "name":"",
            "prop":"",
            "path":[  
               "RealOptionsList9293"
            ],
            "type":"local"
         },
         "value":{  
            "type":11,
            "value":"portland"
         },
         "dependents":[  

         ],
         "save":true
      },
      {  
         "address":{  
            "value":"A4",
            "name":"",
            "prop":"",
            "path":[  
               "RealOptionsList9293"
            ],
            "type":"local"
         },
         "value":{  
            "type":11,
            "value":"portland"
         },
         "dependents":[  

         ],
         "save":true
      }
   ]
}')

Statement:

SELECT d.*
FROM #Data d
CROSS APPLY OPENJSON(d.JsonData, '$.properties') WITH (
   AddressValue nvarchar(1000) '$.address.value',
   ValueValue nvarchar(1000) '$.value.value'
) j
WHERE j.AddressValue = 'A3' AND ValueValue = 'portland'

iSR5 · Accepted Answer · 2019-08-09 23:17:52Z

0

When you want to query JSON arrays or objects you should use JSON_QUERY which is designed to work with that. JSON_VALUE is designed to return a scalar, thus, if you use JSON_VALUE with address, value or even dependents (in your JSON), it'll always returns null. But if you use it with save, it'll return its value.

So, what you need to do is something like this :

SELECT 
    JSON_VALUE([Address],'$.value') 
FROM (
SELECT 
    JSON_QUERY(@json,'$.properties[0].address')  AS [Address]
,   JSON_QUERY(@json,'$.properties[0].value')  AS [Value]
,   JSON_QUERY(@json,'$.properties[0].dependents')  AS [dependents]

) arrays

answered Aug 9, 2019 at 23:17

iSR5

3,5492 gold badges16 silver badges15 bronze badges

2 Comments

frosty Over a year ago

In my case, number of elements in properties array is a variable. Above approach will not work as you may have one or more elements.

iSR5 Over a year ago

@frosty the above approach works fine with the example you've provided. If the json is more complex than this, then JSON_QUERY should do the trick.

Collectives™ on Stack Overflow

Indexing and querying JSON array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related