Get a record count into root JSON path without it repeating?

Question

Gleaning several articles online, including this one with a CTE, and this one WITHOUT a CTE, I have been successful in getting the data I need, including a count of the results. However, I need this count to be in a specific place in the JSON object... Basically, I know how to get a rowset into a specific JSON structure with FOR JSON PATH, ROOT ('data'), etc.

However, I do not know how to get the "recordsFiltered" into the root of my JSON output. This count is is derived using COUNT(*) OVER () AS recordsFiltered

Basically, I need my structure to look like this (see below)... How do I get "recordsFiltered" into the root $. of the JSON result without it repeating a billion times under the "data":[] section?

The best idea I can come up with is to create a temporary table, and then use that to structure the JSON. But, I want to do it the fancy SQL way, if one exists, using SELECT statements or CTEs where applicable.

{
    "draw": 1,
    "recordsTotal": 57, 
    "recordsFiltered": 57,  // <<<--- need records filtered HERE
    "data": [
        {
            "DT_RowId": "row_3",
            "recordsFiltered": "69,420",  //  <<<---- NOT HERE!!!
            "first_name": "Angelica",
            "last_name": "Ramos",
            "position": "System Architect",
            "office": "London",
            "start_date": "9th Oct 09",
            "salary": "$2,875"
        },

        ...
    ]
}

Here is the example SQL code:

 SELECT 
    COUNT(*) OVER () AS recordsFiltered,
    id,
    a,
    b
FROM t1
WHERE 
    (@Search IS NULL OR 
     id LIKE '%'+@Search+'%' OR 
     a LIKE '%'+@Search+'%' OR 
     b LIKE '%'+@Search+'%')
ORDER BY
    CASE 
        WHEN @SortDir = 'ASC' THEN 
            CASE @SortCol
                WHEN 0 THEN id
                WHEN 1 THEN a
                WHEN 2 THEN b
            END 
        END desc,
    CASE 
        WHEN @SortDir = 'desc' THEN 
            CASE @SortCol
                WHEN 0 THEN id
                WHEN 1 THEN a
                WHEN 2 THEN b
            END 
        END DESC
OFFSET @DisplayStart ROWS
FETCH NEXT @DisplayLength ROWS ONLY
for json path, root ('data')

looks like you need to use a sub-select stackoverflow.com/questions/45698902/… — Josh Jay
– Josh Jay, Commented Nov 4, 2019 at 20:13

Josh Jay · Accepted Answer · 2019-11-04 21:07:06Z

1

Looks like you need to generate your table results, then use two (or more?) sub-queries

Here's a simplified example:

declare @tbl table (ID int identity, Col1 varchar(50), Col2 int)

insert into @tbl (Col1, Col2) values ('A',1),('B',2),('C',3)

select
    (select count(1) from @tbl) as 'total',
    (select * from @tbl for json path) as 'data'
for json path

produces:

[
    {
        "total": 3,
        "data": [
            {
                "ID": 1,
                "Col1": "A",
                "Col2": 1
            },
            {
                "ID": 2,
                "Col1": "B",
                "Col2": 2
            },
            {
                "ID": 3,
                "Col1": "C",
                "Col2": 3
            }
        ]
    }
]

Without knowing the rest of your code/schema, here's my guess at your needed query:

select
    *
into
    #MyTable
from
    t1
WHERE 
    (@Search IS NULL OR 
     id LIKE '%'+@Search+'%' OR 
     a LIKE '%'+@Search+'%' OR 
     b LIKE '%'+@Search+'%')

select
    (select count(*) from #MyTable) as recordsFiltered,
    (
        select
            id,
            a,
            b
        from
            #MyTable
        ORDER BY
            CASE 
                WHEN @SortDir = 'ASC' THEN 
                    CASE @SortCol
                        WHEN 0 THEN id
                        WHEN 1 THEN a
                        WHEN 2 THEN b
                    END 
                END desc,
            CASE 
                WHEN @SortDir = 'desc' THEN 
                    CASE @SortCol
                        WHEN 0 THEN id
                        WHEN 1 THEN a
                        WHEN 2 THEN b
                    END 
                END DESC
        OFFSET @DisplayStart ROWS
        FETCH NEXT @DisplayLength ROWS ONLY
        for json path
    ) as [data]
for json path

Using a CTE:

with cte as ()
select
    *
from
    t1
WHERE 
    (@Search IS NULL OR 
     id LIKE '%'+@Search+'%' OR 
     a LIKE '%'+@Search+'%' OR 
     b LIKE '%'+@Search+'%')
)
select
    (select count(*) from cte) as recordsFiltered,
    (
        select
            id,
            a,
            b
        from
            cte
        ORDER BY
            CASE 
                WHEN @SortDir = 'ASC' THEN 
                    CASE @SortCol
                        WHEN 0 THEN id
                        WHEN 1 THEN a
                        WHEN 2 THEN b
                    END 
                END desc,
            CASE 
                WHEN @SortDir = 'desc' THEN 
                    CASE @SortCol
                        WHEN 0 THEN id
                        WHEN 1 THEN a
                        WHEN 2 THEN b
                    END 
                END DESC
        OFFSET @DisplayStart ROWS
        FETCH NEXT @DisplayLength ROWS ONLY
        for json path
    ) as [data]
for json path

edited Nov 4, 2019 at 21:07

answered Nov 4, 2019 at 20:54

Josh Jay

1,2502 gold badges14 silver badges27 bronze badges

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

3 Comments

NamedArray Over a year ago

Okay, so you're thinking that the only way to do this is with a temp table, right? Because that was the best idea I could dome up with!?

Josh Jay Over a year ago

You could use a CTE as well, I'm not sure which would perform best. It would likely depend on your table size, indexes, etc.

Josh Jay Over a year ago

I've added an example with a CTE

Collectives™ on Stack Overflow

Get a record count into root JSON path without it repeating?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related