kus
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
<>https://dbfiddle.uk?rdbms=sqlserver_2019&fiddle=f9286a1466afadb22c3edbf1f5e2b616
Top Answer
PeterVandivier
One solution is below
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=012f4a498e1efdf3d0b6a8c51c54412b&hide=7
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.
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=012f4a498e1efdf3d0b6a8c51c54412b&hide=11
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.