How to I prevent repetitive columns in FOR JSON PATH SQL queries?

user_3772763 0
Viewed 170

So, I'm trying to get data from MSSQL to update some fields in an HTML form, which includes 1 checkbox and a set of options for a select input.

I thought I was being smart by writing my query as shown below. It gets BOTH the two fields at once, instead of two independent queries... I mean, it's OKAY, but I have a lot of repeated items.

Is there a way to flatten this out?

// how do I flatten this
{
    "Calculated": [
        {
            "Calculated": false
        }
    ],
    "Schedule": [
        {
            "Schedule": "THX-1138"
        },
        {
            "Schedule": "LUH-3417"
        },
        {
            "Schedule": "SEN-5241"
        }
    ]
}


// into something more like this?

{
    "Calculated": false,
    "Schedule": [
        "THX-1138",
        "LUH-3417",
        "SEN-5241"
    ]
}

here is the query:

declare
    @EffectDate smalldatetime = '07-01-2012'
    ,@Grade varchar(3) = '001'
    ,@Schedule varchar(9) = 'THX-1138'
    ,@Step smallint = '15'
    ,@jsonResponse nvarchar(max)
;

select @jsonResponse = (

    select
        [Calculated] =
            (
                select
                    b.Calculated
                from
                    tblScalesHourly a
               inner join
                   tblSchedules b
                   on a.EffectDate = b.EffectDate
                      and a.Schedule = b.Schedule
                where
                    a.EffectDate = @EffectDate
                    and a.Schedule = @Schedule
                    and a.Grade = @Grade
                    and a.Step = @Step
                for json path
            )
        ,[Schedule] =
            (
                select
                    Schedule
                from
                    tblSchedules
                where
                    EffectDate = @EffectDate
                    and Calculated = 0
                order by
                    Schedule asc
                for json path
            )
        for json path, without_array_wrapper
)
sql-server json
1 Answers

It's probably a late answer, but I'm able to reproduce this issue with the following test data:

declare @jsonResponse nvarchar(max)
select @jsonResponse = (
    select
        [Calculated] =
            (
                select CONVERT(bit, 0) AS Calculated
                for json path
            )
        ,
        [Schedule] =
            (
                select Schedule
                from (values ('THX-1138'), ('LUH-3417'), ('SEN-5241')) tblSchedules (Schedule)
                order by Schedule asc
                for json path
            )
        for json path, without_array_wrapper
)

You can get the expected results with the following approach. Note, that you can't generate a JSON array of scalar values using FOR JSON, so you need to use a string aggregation (FOR XML PATH('') for SQL Server 2016 or STRING_AGG() for SQL Server 2017+):

select @jsonResponse = (
    select
        [Calculated] = (
           select CONVERT(bit, 0) AS Calculated
        )
        ,
        [Schedule] = JSON_QUERY(CONCAT(
           '["',
            STUFF(
               (
                  select CONCAT('","', Schedule)
                  from (values ('THX-1138'), ('LUH-3417'), ('SEN-5241')) tblSchedules (Schedule)
                  order by Schedule asc
                  for xml path('')
               ), 1, 3, ''
            ),
            '"]'
        ))    
        for json path, without_array_wrapper
)

Output:

{"Calculated":false,"Schedule":["LUH-3417","SEN-5241","THX-1138"]}

And finally, using the statement from the question (not tested):

declare
    @EffectDate smalldatetime = '07-01-2012'
    ,@Grade varchar(3) = '001'
    ,@Schedule varchar(9) = 'THX-1138'
    ,@Step smallint = '15'
    ,@jsonResponse nvarchar(max)
;

select @jsonResponse = (
    select
        [Calculated] = (
           select
                    b.Calculated
                from
                    tblScalesHourly a
               inner join
                   tblSchedules b
                   on a.EffectDate = b.EffectDate
                      and a.Schedule = b.Schedule
                where
                    a.EffectDate = @EffectDate
                    and a.Schedule = @Schedule
                    and a.Grade = @Grade
                    and a.Step = @Step
        ),
        [Schedule] = JSON_QUERY(CONCAT(
           '["',
            STUFF(
               (
                  select CONCAT('","', Schedule)
                  from
                    tblSchedules
                  where
                      EffectDate = @EffectDate
                      and Calculated = 0
                  for xml path('')
               ), 1, 3, ''
            ),
            '"]'
        ))    
        for json path, without_array_wrapper
)
user_65780800
Related