SQL Server OPENJSON returning last element as null

Question

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

s.co.tt · Accepted Answer · 2018-12-06 21:14:19Z

1

You just have one parenthesis out of place. Check out the last line.

SELECT
    uniqueID
,   title
FROM 
    OPENJSON(@response) WITH (
                        uniqueID    NVARCHAR(MAX) '$.uniqueID' 
                    ,   title       NVARCHAR(MAX) '$.title' 
                    ) AS JSON

answered Dec 6, 2018 at 21:14

s.co.tt

1649 bronze badges

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

3 Comments

user1279779 Over a year ago

Thanks, that's it. I swear that I copied this from somewhere that worked, I must be slowly going blind.

s.co.tt Over a year ago

I've been there.. I think one misplaced paren has been the downfall of us all at one time or another. :)

user1279779 Over a year ago

I think that I intentionally used it inside the parens elsewhere in order to force some formatting on a result, and thus didn't realize that I picked a bad example to copy.

Collectives™ on Stack Overflow

SQL Server OPENJSON returning last element as null

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related