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.

CodePudding user response：

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.

CodePudding user response：

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

• Example db<>fiddle

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:

• Example db<>fiddle

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:

• Example db<>fiddle

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