CODEWITHSUNDEEP
jsonsql-server
William Walseth
William Walseth

Reputation: 2923

T-SQL query to return JSON array of strings

I'm hoping to build an optimized data JSON structure that only includes data, no names. I'll included the names in another JSON.

For example

[["1", "William"],["2", "Dylan"]]

I'm looking at "for json auto", running a query like this.

declare @t table(id int, name varchar(20))

insert into @t (id, name) values( 1, 'William')
insert into @t (id, name) values( 2, 'Dylan')

declare @result as varchar(max)

select id, name from @t for json auto

However it includes the names with every value.

[{"id":1,"name":"William"},{"id":2,"name":"Dylan"}]

Is there a way to instruct SQL Server to omit the names and just return a string array?

I'll need to update a couple hundred queries, so I'm hoping for an answer that doesn't require too much modification on a basic query.

Upvotes: 1

Views: 2143

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Not all that different from Charlie's but uses CONCAT_WS to remove some of the explicit " characters:

SELECT [json] = '[' 
       + STRING_AGG('["' + CONCAT_WS('", "', id, 
         STRING_ESCAPE(COALESCE(name,''), N'JSON')) 
       + '"]', ',') + ']'
FROM @t;

Output (after adding a 3rd row, values(3, NULL):

json
[["1", "William"],["2", "Dylan"],["3", ""]]

If you want the literal string null with no quotes:

SELECT [json] = '[' 
       + STRING_AGG('[' 
       + CONCAT_WS(', ', CONCAT('"', id, '"'), 
           COALESCE('"' + STRING_ESCAPE(name, N'JSON') + '"', 'null'))
       + ']', ',') + ']'
FROM @t;

Output:

json
[["1", "William"],["2", "Dylan"],["3", null]]

If you don't want the NULL value to present a column in the JSON, just remove the COALESCE:

SELECT [json] = '[' 
       + STRING_AGG('["' + CONCAT_WS('", "', id, 
         STRING_ESCAPE(name, N'JSON')) 
       + '"]', ',') + ']'
FROM @t;

Output:

json
[["1", "William"],["2", "Dylan"],["3"]]

If you don't want that row present in the JSON at all, just filter it out:

FROM @t WHERE name IS NOT NULL;

If that column doesn't allow NULLs, state it explicitly so we don't have to guess (probably doesn't hurt to confirm id is unique, either):

declare @t table(id int UNIQUE, name varchar(20) NOT NULL);

Upvotes: 1

Charlieface
Charlieface

Reputation: 72436

Unfortunately, SQL Server does not support the JSON_AGG function or similar. You can hack it with STRING_AGG and STRING_ESCAPE.

You can either do this with a single aggregation and concatenating the row together

SELECT '[' + STRING_AGG(CONCAT(
    '["',
    id,
    '","',
    STRING_ESCAPE(name, 'json'),
    '"]'
    ), ',') + ']'
FROM @t t;

Or with a nested aggregation, aggregating first each row in an unpivoted subquery, then all rows together

SELECT '[' + STRING_AGG('[' + j.json + ']', ',') + ']'
FROM @t t
CROSS APPLY (
    SELECT STRING_AGG('"' + STRING_ESCAPE(value, 'json') + '"', ',')
    FROM (VALUES
        (CAST(id AS nvarchar(max))),
        (name)
    ) v(value)
) j(json);

db<>fiddle

I've assumed columns are not nullable. Nullable columns will need special handling, I leave it as an exercise to the reader.

Upvotes: 1

Related Questions