SQL Server: translate Contains over JSON primitive collections to the new JSON_CONTAINS()

[roji]

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-contains-transact-sql?view=sql-server-ver17

At the time of writing, the json type isn't supported as a search value in JSON_CONTAINS() (see limitations in doc page above), so we can't support structural collections.

Note also that JSON_CONTAINS doesn't (currently) support searching for nulls. As a result, we can only translate when either the item or the collection's elements are non-nullable.

[roji]

Note: JSON_CONTAINS() currently doesn't handle searching for nulls inside colllections:

SELECT JSON_CONTAINS(CAST('[1, 2, 3]' AS JSON), NULL); -- Error, search term must be typed
SELECT JSON_CONTAINS(CAST('[1, 2, 3]' AS JSON), CAST(NULL AS int)); -- 0 (not NULL!).
SELECT JSON_CONTAINS(CAST('[1, 2, 3, null]' AS JSON), CAST(NULL AS int)); -- 0 (unexpected)
SELECT JSON_CONTAINS(CAST('[1, 2, 3, null]' AS JSON), CAST(NULL AS json)); -- Argument data type json is invalid for argument 2 of json_contains function.

One possible future improvement is for JSON_CONTAINS() to accept the JSON data type, at which point it will be possible to use the last variant above.

Note that we can still translate when we know the item is non-nullable (e.g. a non-null constant or a non-nullable column), or when we know that the collection has non-nullable elements.