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)
);

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?

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Aug 11, 2022 at 15:11
thanks for your reply @YitzhakKhabinsky , i've added a table creation script for reproduction purposes. — Ceci Semble Absurde.
– Ceci Semble Absurde., Commented Aug 11, 2022 at 15:23

Charlieface · Accepted Answer · 2022-08-11 22:51:38Z

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

answered Aug 11, 2022 at 22:51

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to generate empty JSON array in SQL Server?

DDL and sample data population

The request

Expectation when no rows are returned

Reality when no rows are returned

An ugly solution

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

DDL and sample data population

The request

Expectation when no rows are returned

Reality when no rows are returned

An ugly solution

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related