Why can't I union 2 rows with JSON_ARRAY() in T-SQL?

Question

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?

PS: although the documentation says this should work in Azure SQL, it doesn't work for me there. I could test this with the SQL Server 2022 Docker image.

Answer 1

Hi @Sam Debruyn Thank you for being patient while checking on this.

I have checked with the product group and seems to be a bug. There is a fix for that. It will be in SQL Server 2022 CU1 only. Workaround is to do:
SELECT JSON_ARRAY() COLLATE database_default UNION ALL SELECT JSON_ARRAY() COLLATE database_default. Same applies for JSON_OBJECT.

Please let me know if that works

Regards,
Oury