How to use JSON path wildcards in JSON_QUERY?

Question

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.

Answer 1

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.

• Edmund: An suggestion has been posted on Microsoft's site (908035).
• Edmund: 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.