dotnet / dotnet/efcore

Use OPENJSON array element identity when it becomes available

Open
#33,623 0 comments 0 reactions 0 assignees View on GitHub
area-perf area-query area-sqlserver blocked customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.