Extract values from JSON text field for use in "IN" statement SQL

Question

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 json_ids_field.

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.

Answer 1

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);