Is there any method to create JSON (Array of String) Directly from Select statement in sql?

Question

I have to run select statement on a Table [Admin].[User]. This table has a column Username.

I want to get all the Usernames in a single select statement in a single varchar variable which may look like this:

["some@gmail.com", "myself@yahoo.com", "none@outlook.com"]

Please add a tag that makes it clear what database (and version) you are using. — Jiri Tousek, May 21 '19 at 11:28

Amitabh Das · Answer 1 · 2019-05-21T11:47:37.093

If you are are using mysql you can use the function "JSON_ARRAY(column_name)" to get JSON values Refer to https://dev.mysql.com/doc/refman/5.7/en/json.html for further details. Example/Sample

SELECT JSON_ARRAY(id, name) FROM division

Resulting

JSON_ARRAY(id, name)
[1, "West Bengal"]
[2, "24 PARAGANAS SOUTH"]
[3, "Baruipur"]
[4, "BEGAMPUR G.P."]
[5, "HARAL (P)"]

However, if you have to get an object then USE JSON_OBJECT('key1', column_name1, ....,'keyn', coulmnn) Where key is a string and the other is the fieldname from a table.

Hope this helps.

With respect to your question, where you want just the Username (and NOT JSON) you can use the GROUP_CONCAT (ref: https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat) which will just produce a comma-separated (default comma refer to the documentation to change the separator) values.

SELECT CONCAT( '["' , GROUP_CONCAT(Username SEPARATOR '", "') , '"]') FROM 
FROM tableName GROUP BY Username

Though the user has not specified which database is he seeking solutions for (initially), later, he has added "[]" thus guessing that he is referring to SQLServer, if so, please follow the other answer by @jiri.

Note that the original question had square brackets around table name and in the desired output - someone else than original author edited them out, I just returned them back. This might impact both desired output and what DB we assume the author uses. — Jiri Tousek, May 21 '19 at 11:39
also read the following question it might help you ref: [link] (https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Amitabh Das, May 21 '19 at 11:49

Jiri Tousek · Answer 2 · 2019-05-21T11:27:23.083

Since it looks like you are using Microsoft SQL Server, you should be able to do this using SELECT ... FOR JSON clause.

The format of output is however somewhat different, the closest I could get to yours is:

[{"username":"some@gmail.com"},{"username":"myself@yahoo.com"},{"username":"none@outlook.com"}]

Some more related documentation: https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017

SQL Fiddle: http://sqlfiddle.com/#!18/9eecb/44805

Is there any method to create JSON (Array of String) Directly from Select statement in sql?

2 Answers2