获取记录计数到根JSON路径，而无需重复？

2024-04-27 • 问答

在线收集了几篇文章，包括这篇with a CTE和这篇WITHOUT a CTE，我已经成功地获取了我需要的数据，包括对结果的计数。但是，我需要将此计数放置在JSON对象中的特定位置...基本上，我知道如何使用FOR JSON PATH,ROOT ('data')等将行集转换为特定的JSON结构。

但是，我不知道如何将“ recordsFiltered”放入我的JSON输出的根目录中。此计数是使用COUNT(*) OVER () AS recordsFiltered

得出的

基本上，我需要我的结构看起来像这样（如下所示）...如何将“ recordsFiltered”放入JSON结果的根$.中，而又不使其在{{1 }}部分？

我能想到的最好的主意是创建一个临时表，然后使用 that 构造JSON。但是，如果有的话，我想使用合适的SELECT语句或CTE来做精美的SQL方式。

"data":[]

这是示例SQL代码：

{
    "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"
        },...
    ]
}

看起来您需要生成表格结果，然后使用两个（或更多？）子查询

这是一个简化的示例：

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

产生：

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

在不知道其余代码/架构的情况下，这是我对所需查询的猜测：

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

使用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,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

获取记录计数到根JSON路径，而无需重复？

gaoy969 回答：获取记录计数到根JSON路径，而无需重复？

大家都在问