Output ordinal position

Question

Output ordinal position

Ryan Abbey 1,186

Given a JSON string containing an array, is there any way to output the ordinal position of each element in the array?

"arrayelem": [{"name": "ColumnX"},
{"name": "ColG"},
{"name": "ColHIG"}]

So given above, I want to get output

Name Ordinal

ColumnX 0 (or 1... just right order important)
ColG 1
ColHIG 2

Using row_number() over (order by (select 1)) gives me the perceived correct order but how certain can we be it will always be the correct order? Is there a more definitive option?

I've added synapse as a tag as the requirement is for processing within Synapse so if there is an easy solution that can be done in Data Factory or similar (not spark due to ramp up times), I'm ears on that too

Accepted answer

0 additional answers

Your answer

Answer 1

Guoxiong 8,206

Based on the Microsoft documentation, the OPENJSON() function with the default schema returns Key/Value pair and the type of the Value. So you may use the key as the Ordinal. Here is the example:

DECLARE @json nvarchar(max) = N'
{
    "arrayelem": [
        {"name": "ColumnX"},
        {"name": "ColG"},
        {"name": "ColHIG"}
    ]
}
';

SELECT JSON_VALUE([Value], '$.name') AS [Name], [key] AS Ordinal
FROM OPENJSON(@json, '$.arrayelem');

Ryan Abbey 1,186 Reputation points

2021-11-12T00:44:33.527+00:00

Fantastic!! Thank you!

Share via

Output ordinal position

Name Ordinal

0 additional answers

Your answer