获取记录计数到根JSON路径中，而无需重复? [英] Get a record count into root JSON path without it repeating?

问题描述

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

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.

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

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

基本上，我需要我的结构看起来像这样(见下文)...如何将"recordsFiltered"放入JSON结果的根$.中，而不使它在"data":[]部分下重复十亿次?

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?

我能想到的最好的主意是创建一个临时表，然后使用 that 构造JSON.但是，如果有的话，我想使用一种喜欢的SQL方式，在适用的情况下使用SELECT语句或CTE.

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"
        },

        ...
    ]
}

这是示例SQL代码:

 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 generate your table results, then use two (or more?) sub-queries

这是一个简化的示例:

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
            }
        ]
    }
]

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

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

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

这篇关于获取记录计数到根JSON路径中，而无需重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！