Allow support for JSON of simple arrays generation - by weaaddar

Status : 


Sign in
to vote
ID 1383569 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 5/30/2015 9:11:35 AM
Access Restriction Public


Currently, there is no way in SQL 2016 CTP2 to generate an array of primitive value. (e.g. [1,2,3] or [true,"dog",1]). 

Sometimes you do not need an object to send down a correlated list of values. E.g. 
select author, book_name, genre, user_tags=(select tag from book_user_tags t where b.book_id = t.book_id for json auto)
from books b;

Here I do not need the heaviness of [{"tag":"thriller"},{"tag":"action"}], I just would want ["thriller"."action"]. 

perhaps a new clause can be added to generate a simple array, the stipulation being of course, that you can only do so if there is one column in the result set.

Parsing information isn't available, but it would be useful to also be able to parse simple arrays.
Sign in to post a comment.
Posted by MKipp on 5/18/2017 at 12:46 AM
This simple array should also be possible with objects, instead of just primitive values.

The problem I have is that the array that I can create from a pure relations query, can't be matched by a similar query from data from a JSON field.
For example SELECT firstName, lastName FROM person WHERE lastName = 'Johnson' FOR JSON PATH will create an array LIKE
[{ "firstName": "John", "lastName": "Johnson" }, { "firstName": "Sara", "lastName": "Johnson" }].

When I have { "firstName": "John", "lastName": "Johnson" } and { "firstName": "Sara", "lastName": "Johnson" } stored in a JSON field, I am not able to run a simple query to create the same array.
SELECT JSON_QUERY(personJSON) personInfo FROM person WHERE lastName = 'Johnson' FOR JSON PATH will create
[{ "personInfo": { "firstName": "John", "lastName": "Johnson" }}, { "personInfo": { "firstName": "Sara", "lastName": "Johnson" }}].