SELECT JSON_VALUE From table returns null instead of value

sql json sql-server json-value

Gibin Jacob Job · Jan 7, 2019 · Viewed 9.9k times · Source

JSON stored in a column 'DataJson' in table

[{
    "KickOffDate": "1-Jan-2019",
    "TeamSize": "11",
    "ClientEngineer": "Sagar",
    "WaitingPeriod": "16.5"
}]

Query

SELECT JSON_VALUE(DataJson,'$.KickOffDate') AS KickOffDate
     , JSON_VALUE(DataJson,'$.ClientEngineer') AS ClientEngineer
FROM [ABC].[Deliver]

Result

KickOffDate   ClientEngineer
NULL          NULL

Result should be:

KickOffDate   ClientEngineer
1-Jan-2019    Sagar

Answer

Your sql query is wrong.
You have to correct query like below.

 SELECT JSON_VALUE(DataJson,'$[0].KickOffDate') AS KickOffDate ,JSON_VALUE(DataJson,'$[0].ClientEngineer') AS ClientEngineer FROM [ABC].[Deliver]

The data stored in table is not JSON Object, it's JSON Array.
So in order to get each value of JSON Object, need to set index of JSON Object in JSON Array.
Otherwise, you can store data as JSON Object, and then your query can be work normally.

SELECT JSON_VALUE From table returns null instead of value

Answer

Related questions