How to generate empty JSON array in SQL Server?

Question

On SQL Server, I have an request generating a list of object in JSON format using FOR JSON PATH. But I can't manage to handle the case where no rows are returned.

DDL and sample data population

Create a table nammed mytable:

CREATE TABLE [dbo].[mytable] (
    [field_a] VARCHAR (50)     NULL,
    [field_b] VARCHAR (50)     NULL,
    [id]      UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,
    CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED ([id] ASC)
);

Database used: Microsoft SQL Azure (RTM) - 12.0.2000.8 May 12 2022 23:11:24 Copyright (C) 2022 Microsoft Corporation

The request

SELECT
 [mytable].[field_a] as [a],
 [mytable].[field_b] as [b]
FROM
 [mytable]
FOR JSON PATH, INCLUDE_NULL_VALUES

Expectation when no rows are returned

When rows are returned the request should return the following structure:

[]

Reality when no rows are returned

The request as it's written return an empty result instead:

An ugly solution

I have seen the WITHOUT_ARRAY_WRAPPER option that I combined with CONCAT:

SELECT
 CONCAT(
  '[',
  (
   SELECT
    [mytable].[field_a] as [a],
    [mytable].[field_b] as [b]
   FROM
    [mytable]
   FOR JSON PATH, INCLUDE_NULL_VALUES
  ),
  ']'
 ) as [jsonResult]

It's working in my case, but I think it's a bit ugly.

Is there a way to improve this request to display the array brackets whether there are rows or not?

Answer 1

The FOR JSON operator does not return anything at all if there are no rows to serialize.

You can instead use ISNULL to return an empty array

SELECT ISNULL((
    SELECT
     t.field_a as a,
     t.field_b as b
    FROM
     mytable t
    FOR JSON PATH, INCLUDE_NULL_VALUES
  ), '[]');

db<>fiddle