Use OPENJSON array element identity when it becomes available
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
OPENJSON currently doesn't allow projecting out the index of elements in the JSON array ("array element identity"), when the WITH clause is used. This feature does exist in Synapse ([docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16#array-element-identity)), but not in any other version.
As a result, we currently identify OPENJSON usages where ordering matters, and rewrite them to OPENJSON without WITH, which is less efficient, doesn't support all types (e.g. varbinary can't be used because casting from base64 string data isn't supported), and adds complexity. On the SQL Server side, bringing over array element identity is planned - switch to that when that's done.
Contributor guide
Assessment
This issue has not been assessed yet.