In Azure SQL server. I have the following (non-functional) query that is attempting to use the values from a JSON array of foreign keys to find rows, using "IN".
SELECT * FROM foo_table WHERE foo_table_id IN (SELECT json_ids_field FROM bar_table WHERE bar_table_id _field= 1);
However this returns an error because it does not natively understand the JSON format found in
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value '[29,56,57,71,138,145,195]' to data type int
How can I deserialize this field so that its values are aggregated into a list that can be used in the IN clause.
As mentioned in the comment, you need to use
OPENJSON to consume your JSON and convert it into a data set:
SELECT * FROM dbo.foo_table WHERE foo_table_id IN (SELECT TRY_CONVERT(int, OJ.value) FROM dbo.bar_table bt CROSS APPLY OPENJSON(bt.json_ids_column) OJ WHERE bt.bar_table_id_column = 1);