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.ChargeArray.chargesPerLocation') > 1
One solution is below
The subselect in the above query works by indexing into the json document to path `$.Decorations.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.