JSON_VALUE SQL Server function not returning all values

Question

I have this nvarchar field with a JSON like this:

{"BOARD":"QC_Reference_Phone","SERIAL":"LGM700c2eee454","VERSION.INCREMENTAL":"1901817521542","CPU_ABI2":"armeabi","HOST":"CLD-BLD3-VM1-16","TIME":"1547801577000","MODEL":"LG-M700","MANUFACTURER":"LGE","USER":"jenkins","CPU_ABI":"armeabi-v7a","BRAND":"lge","DISPLAY":"OPM1.171019.026","FINGERPRINT":"lge/mh_global_com/mh:8.1.0/OPM1.171019.026/1901817521542:user/release-keys","HARDWARE":"mh","PRODUCT":"mh_global_com","BOOTLOADER":"unknown","VERSION.RELEASE":"8.1.0","ID":"OPM1.171019.026","UNKNOWN":"unknown","TYPE":"user","VERSION.SDK.NUMBER":"27","TAGS":"release-keys"}

And so my syntax is:

select JSON_VALUE(DeviceHardwareData,'$.VERSION.SDK.NUMBER') SDKVersion_nbr
FROM MyTable

It will work with all the other values within the JSON field but for "VERSION.SDK.NUMBER". It returns a NULL Result for every row in my table. I can actually get the value with the OPENJSON function, but I would like to know why it's specifically not returning the value for that attribute using JSON_Value

Roger Wolf · Accepted Answer · 2019-12-18 23:35:48Z

2

It doesn't work as you expect because in JSON Path syntax, the full stop character means "going one level down to a nested element under the following name". In order to extract the value with your path expression, your JSON structure should resemble the following:

"VERSION": {
    "SDK": {
        "NUMBER": 14
    }
}

However, enclosing the element name in doublequotes in the path expression apparently does the trick:

declare @j nvarchar(max) = N'{
    "VERSION.SDK.NUMBER": "27",
    "VERSION": {
        "SDK": {
            "NUMBER": 14
        }
    }
}';

select json_value(@j, '$."VERSION.SDK.NUMBER"') as [TopValue],
    json_value(@j, '$.VERSION.SDK.NUMBER') as [NestedValue];

answered Dec 18, 2019 at 23:35

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON_VALUE SQL Server function not returning all values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related