FOR JSON - How can I create JSON array output without keys?

Question

I'm playing around with the FOR JSON features in SQL Server 2019, and I'm struggling with how to use that feature to create a JSON list without keys... so like just a straight-up list of values.

For example, say I have this SQL:

declare @years table (year int)
insert into @years values (2022),(2021),(2020)

 SELECT year
        FROM @years
        FOR JSON AUTO

This creates output like so: [{"year":2022},{"year":2021},{"year":2020}]

But how would I get it to create output like THIS instead: [2022,2021,2020]

And I'm looking to do this without messy nested string replaces... but maybe that's the only way? Thanks a lot for any help!

Does this answer your question? SQL to JSON - array of objects to array of values in SQL 2016 — GSerg
– GSerg, Commented Jun 16, 2024 at 12:58

Yitzhak Khabinsky · Accepted Answer · 2022-12-22 23:08:31Z

2

Please try the following solution.

It will work starting from SQL Server 2017 onwards.

SQL

DECLARE @tbl TABLE ([year] int);
INSERT INTO @tbl VALUES (2022),(2021),(2020);

SELECT json = QUOTENAME(STRING_AGG([year], ','))
FROM @tbl;

Output

json
[2022,2021,2020]

answered Dec 22, 2022 at 23:08

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thom A Over a year ago

Note that if STRING_AGG returns a value longer than 128 characters, it will be trucates with this solution. This shouldn't be an issue with this example, but it could be with others .

Yitzhak Khabinsky Over a year ago

Sample table and data show INT data type.

Thom A Over a year ago

Yes, but STRING_AGG returns a string, and more importantly QUOTENAME expects a sysname; so if you have a value returned by STRING_AGG that's more than 128 characters, then truncation occurs.

Collectives™ on Stack Overflow

FOR JSON - How can I create JSON array output without keys?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related