For a minimal working example say I have a column storing JSON with a structure similar to the following.

DECLARE @json NVARCHAR(4000) = 
      {"Name":"Apple", "Baking": ["Pie","Tart"], "Plant":"Tree"},
      {"Name":"Tomato", "Cooking":["Stew","Sauce"], "Plant":"Vine"},
      {"Name":"Banana","Baking":["Bread"], "Cooking":["Fried"], "Plant":"Arborescent"}

This is valid JSON and scalars can be returned.

SELECT JSON_VALUE(@json,'$.Dims[0].Name');

However, when I use JSON_QUERY to return the names of all of the "Dims" SQL Server 2017 errors.

SELECT JSON_QUERY(@json,'$.Dims[*].Name');
Msg 13607, Level 16, State 4, Line 16
JSON path is not properly formatted. Unexpected character '*' is found at position 7.

This should return

["Apple", "Tomato", "Banana"]

This is a valid standard JSON path. Any ideas why this is not working and how to get this to work on SQL Server 2017.


Looks like that is not currently supported, and would make a good feedback item.

You can get the names into a resultset with a query like:

select json_value(v.value,'$.Name') Name
from openjson(@json,'$.Dims') v

And from there you can construct a new JSON doc, but if you want an array of primitives, you need to do something like this.

select concat('[',string_agg( quotename(json_value(v.value,'$.Name'), '"'), ', ')  WITHIN GROUP ( ORDER BY [key] )   ,']')
from openjson(@json,'$.Dims') v

I don't think there is a way to get an array of primitive values (ie not objects) with FOR JSON queries.

  • An suggestion has been posted on Microsoft's site (908035). – Edmund Jan 22 '19 at 12:53
  • 1
    Thanks for the workaround. The pattern is to select the path down to the items with OPENJSON and then JSON_VALUE on the each item to extract the value. – Edmund Jan 22 '19 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.