0

I am trying to retrieve JSON from SQL Server using FOR JSON PATH that looks like this:

["5.0","5.5","6.0","6.5","7.0","7.5","8.0","8.5","9.0","9.5","10.0","10.5","11.0"]

But I am getting this instead:

[{"value":"10.0"},{"value":"10.5"},{"value":"11.0"},{"value":"5.0"},{"value":"5.5"},{"value":"6.0"},{"value":"6.5"},{"value":"7.0"},{"value":"7.5"},{"value":"8.0"},{"value":"8.5"},{"value":"9.0"},{"value":"9.5"}] 

SELECT        [value]
FROM          [table]
FOR JSON PATH

DECLARE @json_test TABLE ([value] varchar(5))
INSERT INTO @json_test values ('5.0')
INSERT INTO @json_test values ('6.0')
INSERT INTO @json_test values ('7.0')
INSERT INTO @json_test values ('8.0')
INSERT INTO @json_test values ('9.0')
INSERT INTO @json_test values ('10.0')

SELECT [value]
FROM @json_test
FOR JSON PATH
Dale K
  • 25,246
  • 15
  • 42
  • 71
Fadi Kadi
  • 1
  • 1
  • Does this answer your question? [SQL to JSON - array of objects to array of values in SQL 2016](https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016) – Nick.Mc Jul 30 '21 at 01:36

1 Answers1

1

On SQL Server 2017 and later, where string_agg() is available...

with Concatenated (someText) as (
  select string_agg(quotename([value], '"'), ',')
  from @json_test
)
select someArray = json_query(quotename(someText))
from Concatenated
for json path, without_array_wrapper;

Which yields...

{"someArray":["5.0","6.0","7.0","8.0","9.0","10.0"]}
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
  • Instead of `quotename` use `STRING_ESCAPE(..., 'json')` – Charlieface Jul 30 '21 at 09:36
  • How would `string_escape()` help? I'm not replacing or escaping characters with `quote_name()`, I'm using it as a lazy man's `concat()`. – AlwaysLearning Jul 30 '21 at 09:41
  • I assumed you used it to also escape `"` in the values, but it doesn't work properly for json. Best way to do it is `string_agg(cast('"' + string_escape([value], 'json') + '"' as nvarchar(max)), ',')` admittedly it matters less with the current input, but it is `varchar` so it could have anything in there – Charlieface Jul 30 '21 at 09:48