Why is JSON_QUERY sending back a null value?

Question

This is driving me nuts, and I don't understand what's wrong with my approach.

I generate a JSON object in SQL like this:

    select @output = (  
         select distinct lngEmpNo, txtFullName
         from tblSecret
         for json path, root('result'), include_null_values
    )

I get a result like this:

{"result":[{"lngEmpNo":696969,"txtFullName":"Clinton, Bill"}]}

ISJSON() confirms that it's valid JSON, and JSON_QUERY(@OUTPUT, '$.result') will return the array [] portion of the JSON object... cool!

BUT, I'm trying to use JSON_QUERY to extract a specific value:

This gets me a NULL value. Why??????? I've tried it with the [0], without the [0], and of course, txtFullName[0]

SELECT JSON_QUERY(@jsonResponse, '$.result[0].txtFullName');

I prefixed with strict, SELECT JSON_QUERY(@jsonResponse, 'strict $.result[0].txtFullName');, and it tells me this:

Msg 13607, Level 16, State 4, Line 29
JSON path is not properly formatted. Unexpected character 't' is found at 
position 18.

What am I doing wrong? What is wrong with my structure?

Answer 1

JSON_QUERY will only extract an object or an array. You are trying to extract a single value so, you need to use JSON_VALUE. For example:

SELECT JSON_VALUE(@jsonResponse, '$.result[0].txtFullName');