When querying using OPENJSON, will the result set mimic the order of the JSON array or will the order be random?

user_12969410 0
Viewed 746

Let's say I have this JSON array:

[{
    "Field": "1"
}, {
    "Field": "2"
}, {
    "Field": "3"
}]

And I use that in an OpenJSON query in SQL Server.

Will the query result set be always the same order as the array:

Field
1
2
3

Or will it be random just like any normal Select query without an order by?

open-json sql-server json
1 Answers

In case of JSON array and OPENJSON() call with default schema, based on documentation, the result is a table with columns key, value and type, and the key column holds the index of the element in the specified array. If you want to get an ordered set, you should use a statement like the following:

DECLARE @json nvarchar(max) = N'[{ "Field": "1" }, { "Field": "2" }, { "Field": "3" }]'

SELECT [value]
FROM OPENJSON(@json)
ORDER BY CONVERT(int, [key])

-- or

SELECT c.Field
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.[value]) WITH (Field int '$.Field') c
ORDER BY CONVERT(int, j.[key])
user_65780800
Related