In SQL Server 2022 and Azure SQL, the function JSON_ARRAY has been added.
This works:
select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col
This doesn't:
select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col
union all
select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col
The union gives me [S0002][206] Line 1: Operand type clash: nvarchar(max) is incompatible with void type
So why would the union not work?
Seems a bug to me, something happening with union (with or without all) and also with intersect, except, etc. before the JSON transformation converts the array to a simple string. But a join between two subqueries works:
select * FROM
(select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col ) AS x
INNER JOIN
(select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col) AS y
ON x.id = y.id;
I'd suggest filing a bug here (at least at the moment).
In the meantime, you can coerce slightly different behavior using explicit collation against the expressions:
select 1 as id, JSON_ARRAY(1 , 2 , 3)
COLLATE SQL_Latin1_General_CP1_CI_AS as array_col
UNION ALL
select 1 as id, JSON_ARRAY(1 , 2 , 3)
COLLATE SQL_Latin1_General_CP1_CI_AS as array_col;
For integers it probably doesn't matter what collation you use (only that you use the same one for all aligned expressions in the union!) but, for strings, you might instead use:
select 1 as id, JSON_ARRAY(1 , 2 , 3)
COLLATE DATABASE_DEFAULT as array_col
UNION ALL
select 1 as id, JSON_ARRAY(1 , 2 , 3)
COLLATE DATABASE_DEFAULT as array_col;
An uglier workaround could be to forcefully materialize the output of both queries, and union those:
select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col into #x;
select 1 as id, JSON_ARRAY(1 , 2 , 3) as array_col into #y;
select * from #x union all select * from #y;
select top(100000)...etc?