How to use JSON path wildcards in JSON_QUERY? For a minimal working example say I have a column storing JSON with a structure similar to the following.

DECLARE @json NVARCHAR(4000) = 
    '{"Dims": 
     [
      {"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 ISJSON(@json);



1



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



Apple



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.