2

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?

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Kevin Ganancial
  • 37
  • 7

1 Answers1

0

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])
Zhorov
  • 28,486
  • 6
  • 27
  • 52