Using numeric field names in SQL JSON [duplicate]

Question

I have the following code example:

    Insert into dbo.Data (
    COL_0
select
        A_1
from OPENJSON (@jsonData)
    with (
        A_1 nvarchar(max)   '$.0'
)

which triggers:

    ex  {System.Data.SqlClient.SqlException (0x80131904): JSON path is not properly formatted. Unexpected character '0' is found at position 2.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader()    at System.Data.SqlClient.SqlCommand.EndExecu…   

I was thinking it was related to the fact that the field name in the json structure is "0" and maybe numeric field names arent allowed? My json structure is like this:

[{"0":"","1":"January 1, 2024"}]

Thanks

Answer 1

The problem is two fold. First, you're not referencing your array, you're trying to go directly to $.0 which is incorrect because at the top level array, there is no key named "0".

Second, you need to wrap the key in double quotes if it's yelling at you for being an integer key. i.e. $.0 becomes $."0".

Here are several ways you can get at the keys you're looking for.

If you have more than one item in the array

declare @json nvarchar(max) = N'[{"0":"","1":"January 1, 2024"}]'

select *
from openjson(@Json) a
cross apply openjson(a.Value) 
with
(
    [0] nvarchar(4000) '$."0"',
    [1] nvarchar(4000) '$."1"'
) b

If you have one item in the array

declare @json nvarchar(max) = N'[{"0":"","1":"January 1, 2024"}]'

select 
    [0] = json_value(@Json, '$[0]."0"'),
    [1] = json_value(@Json, '$[0]."1"')

or

select *
from openjson(@Json, '$[0]')
with
(
    [0] nvarchar(4000) '$."0"',
    [1] nvarchar(4000) '$."1"'
) a