I have a SQL Server query that produces the following result:
DECLARE @chartData TABLE
(
mSales MONEY
)
INSERT INTO @chartData (mSales)
SELECT 498.65 AS mSales
UNION ALL
SELECT 439.83
UNION ALL
SELECT 492.23
UNION ALL
SELECT 439.40;
SELECT
'IPM Sales' AS chartTitle,
(SELECT * FROM @chartData FOR JSON PATH) AS chartData
FOR JSON PATH
Output:
[
{
"chartTitle": "IPM Sales",
"chartData": [
{"mSales": 498.6500},
{"mSales": 439.8300},
{"mSales": 492.2300},
{"mSales": 439.4000}
]
}
]
Expected Output:
[
{
"chartTitle": "IPM Sales",
"chartData": [
498.6500,
439.8300,
492.2300,
439.4000
]
}
]
Basically, I want the chartData to be a list of values, not objects with mSales keys. If I use the WITHOUT_ARRAY_WRAPPER keyword, it converts the values to strings. How can I achieve the expected result?
STRING_AGGis the only currently supported method, butJSON_ARRAYAGGis coming soon learn.microsoft.com/en-us/sql/t-sql/functions/…