SELECT JSON_VALUE From table returns null instead of value

Question

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

Are you using SQL Server? If not, then please tell us your database. — Tim Biegeleisen, Jan 07 '19 at 14:09
Firstly look at [this](https://stackoverflow.com/help/how-to-ask). — Hamlet Hakobyan, Jan 07 '19 at 14:09
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Jan 07 '19 at 14:16
@a_horse_with_no_name A Google search for `"JSON_VALUE SQL"` seems to bring up only SQL Server stuff. — Tim Biegeleisen, Jan 07 '19 at 14:20
@TimBiegeleisen: so I am supposed to search the internet to find the correct DBMS? And besides: JSON_VALUE is part of the SQL standard and Oracle, DB2 and MariaDB also support JSON_VALUE. — , Jan 07 '19 at 14:23
@a_horse_with_no_name Don't look at me, I just work here! :P — Tim Biegeleisen, Jan 07 '19 at 14:24

score 12 · Answer 1 · answered Jan 07 '19 at 14:28

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.

score 2 · Answer 2 · answered Jan 07 '19 at 14:18

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;

Demo

Here is what the input JSON I used looks like:

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

SELECT JSON_VALUE From table returns null instead of value

2 Answers2

Demo