sql-server add tag
hi guys, I am new to json in sql server and was wondering if you could help me understand how to use where in an array like if this property for any array item is greater than one, select that. This is what I have so far. 

Problem is I don’t want to specify the array position. I want it for any array position JSON_VALUE(AttributeValue, '$.Decorations[0].ChargeArray[1].chargesPerLocation') > 1

Top Answer
One solution is below


The subselect in the above query works by indexing into the json document to path `$.Decorations[0].ChargeArray` using [`OPENJSON`](https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql). You need to preserve the PK for the outer document and return it to the outer select if you want perform inner filtering but still return the full document (or any attributes not nested at-or-below the filter) as you've asked.


In the preceding query, you can see the document PK as well as a tabular transform of the inner array suitable for filter with SQL syntax.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.