1

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?

2
  • 1
    Might be easier to use STRING_AGG to reduce (GROUP BY) all to charTitle, chartData (where chartData is now a comma separated list), then use JSON Commented Mar 20 at 23:56
  • Indeed, STRING_AGG is the only currently supported method, but JSON_ARRAYAGG is coming soon learn.microsoft.com/en-us/sql/t-sql/functions/… Commented Mar 21 at 0:26

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.