CODEWITHSUNDEEP
sqljsonsql-serverjson-value
Gibin Jacob Job
Gibin Jacob Job

Reputation: 59

SELECT JSON_VALUE From table returns null instead of value

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

Upvotes: 5

Views: 18648

Answers (2)

mark Oriend
mark Oriend

Reputation: 165

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.

Upvotes: 12

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Your JSON appears to be malformed, at least from the point of view of SQL Server's JSON API. From what I have read, if your JSON data consists of a top level JSON array, then the array needs to have a key name, and also the entire contents should be wrapped in { ... }.

The following setup has been tested and works:

WITH yourTable AS (
    SELECT '{ "data" : [{"KickOffDate": "1-Jan-2019", "TeamSize": "11", "ClientEngineer": "Sagar", "WaitingPeriod": "16.5"}] }' AS DataJson
)

SELECT
    JSON_VALUE(DataJson, '$.data[0].KickOffDate') AS KickOffDate,
    JSON_VALUE(DataJson, '$.data[0].ClientEngineer') AS ClientEngineer
FROM yourTable;

enter image description here

Demo

Here is what the input JSON I used looks like:

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

Upvotes: 2

Related Questions