1

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!

Dale K
  • 25,246
  • 15
  • 42
  • 71
c_tothe_k
  • 61
  • 7

1 Answers1

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]
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
  • 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 . – Thom A Dec 22 '22 at 23:36
  • Sample table and data show INT data type. – Yitzhak Khabinsky Dec 22 '22 at 23:38
  • 3
    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. – Thom A Dec 23 '22 at 08:57