I'm trying to use OPENJSON to return values from an array of JSON objects, but the last requested element is always coming up NULL.
The basic object is:
DECLARE
@response NVARCHAR(MAX) = '[
{
"uniqueID":"T0139"
, "title":"Item 1"
},
{
"uniqueID":"J0295"
, "title":"Item 3"
}
]'
SELECT ISJON(@response) returns true.
If I run:
SELECT
uniqueID
, title
FROM
OPENJSON(@response) WITH (
uniqueID NVARCHAR(MAX) '$.uniqueID'
, title NVARCHAR(MAX) '$.title'
AS JSON)
I get back:
uniqueID title
T0139 NULL
J0295 NULL
However, if I throw a bogus column on the back of my statement:
SELECT
uniqueID
, title
, doesntExist
FROM
OPENJSON(@response) WITH (
uniqueID NVARCHAR(MAX) '$.uniqueID'
, title NVARCHAR(MAX) '$.title'
, doesntExist NVARCHAR(MAX) '$.doesntExist'
AS JSON)
Now I get back a value for title:
uniqueID title doesntExist
T0139 Item 1 NULL
J0295 Item 3 NULL
The data is clearly there, and returnable. So why is it that this only works when I request a missing node? What's wrong with my first statement?
DB Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=aa985e8007dedbc5dea9b6b7e4243826