Build JSON string without repeating results in result set

Question

I don't know how to stop repeating a common value when building a JSON string in TSQL. I need the total count of the sub-select in the root of the JSON structure, and not repeated constantly inside the data section. I don't know how to rearrange my SELECT statements to get the JSON string I want.

"recordsFiltered" represents a total number of records that were filtered & returned from a user-initiated search. I only need this number one time in my JSON string.

Here is the DESIRED result:

    {
    "draw": 1,
    "recordsTotal": 137920,
    "recordsFiltered": 55,
    "data": [
        {
            "DT_RowId": 1029833,
            "PositionTitle": "Total SQL Chad",
            "Department": "Nice Suburbs",
        },
        {
            "DT_RowId": 1029834,
            "PositionTitle": "Beta SQL Programmer",
            "Department": "Still Lives with Mom",
        },

    ...
    ]
}

As you can see, this is a pretty standard query, and its result below. "recordsFiltered" repeats often. That is undesirable.

--QUERY BASELINE
select
    count(RowID) over() as recordsFiltered
    ,RowID as DT_RowID
    ,LastName
    ,PositionTitle
from
    badges
where
    LastName = 'Dean'
for json path, root ('data'), include_null_values

--RESULT
{
    "data": [
    {
        "recordsFiltered": 55,
        "DT_RowID": 1005791,
        "LastName": "Dean",
        "PositionTitle": ""
    },
    {
        "recordsFiltered": 55,
        "DT_RowID": 1011253,
        "LastName": "Dean",
        "PositionTitle": "Instructional Assistant"
    },

    ...
    ]
}

Here is personal attempt number 187. See that I try to get the count into a "higher" SELECT statement, but "recordsFiltered" is still part of the data section... What do I need to do to stop or exclude the repeated entries in the final JSON string? This is pretty close...

--ATTEMPT 187
select

    json_value(_.data, '$[0].recordsFiltered') as recordsFiltered
    ,_.data

from
    (
        select
            isnull((
                select
                    count(RowID) over() as recordsFiltered
                    ,RowID as DT_RowID
                    ,LastName
                    ,PositionTitle
                from
                    badges
                where
                    LastName = 'Dean'
                for json path, include_null_values
            ), '[]') as data

    ) _

for json path, include_null_values, without_array_wrapper

--RESULT
{
"recordsFiltered": "55",
"data": [
    {
        "recordsFiltered": 55,
        "DT_RowID": 1005791,
        "LastName": "Dean",
        "PositionTitle": ""
    },
    {
        "recordsFiltered": 55,
        "DT_RowID": 1011253,
        "LastName": "Dean",
        "PositionTitle": "Instructional Assistant"
    },
    ...
    ]
}

============sample data/table============

It's a simple "flat" table, I've exclude some of the columns for "privacy".

create table [dbo].[Badges](
[RowID] [int] identity(1000001,1) not null,
[LastName] [varchar](32) not null,
[PositionTitle] [varchar](64) not null,
)

Answer 1

If I understand you correctly, the next approach may help:

Table:

CREATE TABLE Badges (
   [RowID] [int] identity(1000001,1) not null,
   [LastName] [varchar](32) not null,
   [PositionTitle] [varchar](64) not null,
)
INSERT INTO Badges
   ([LastName], [PositionTitle])
VALUES
   ('Dean', 'Instructional Assistant'),
   ('Dean', 'Instructional Assistant'),
   ('Luke', 'Instructional Assistant')

Statement:

SELECT
   Draw = 1,
   recordsTotals = (SELECT COUNT(*) FROM Badges),
   recordsFiltered = (SELECT COUNT(*) FROM Badges WHERE LastName = 'Dean'),
   Data = (
      SELECT RowID, LastName, PositionTitle
      FROM Badges
      WHERE LastName = 'Dean'
      FOR JSON AUTO
   )
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER     

Result:

{
   "Draw":1,
   "recordsTotal":3,
   "recordsFiltered":2,
   "Data":[
      {
         "RowID":1000001,
         "LastName":"Dean",
         "PositionTitle":"Instructional Assistant"
      },
      {
         "RowID":1000002,
         "LastName":"Dean",
         "PositionTitle":"Instructional Assistant"
      }
   ]
}

Notes:

You need to check the performance of this statement.

Answer 2

The next query should satisfy your requirements.

1- Filter the records and save them to a temporary table.

2- Calculate the affected records.

3- Generate the json with the expected information.

DROP TABLE IF EXISTS [#Filtered];

;WITH
[Filtered]
AS
(
    SELECT
         [RowID]
        ,[LastName]
        ,[PositionTitle]
        ,[Department]
    FROM [dbo].[Badges]
    WHERE ([LastName] = 'Dean')
)
SELECT
    *
INTO [#Filtered]
FROM [Filtered];

DECLARE @RecordsFiltered INT = @@ROWCOUNT;

SELECT
     [draw] = 1
    ,[recordsTotal] = COUNT(1)
    ,[recordsFiltered] = @RecordsFiltered
    ,[data] =
    (
        SELECT
             [DT_RowId] = [F].[RowID]
            ,[F].[PositionTitle]
            ,[F].[Department]
        FROM [#Filtered] AS [F]
        FOR JSON PATH
    )
FROM [dbo].[Badges] AS [B]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

-- Here another version without use temporary tables

;WITH
[Filtered]
AS
(
    SELECT
         [RowID]
        ,[LastName]
        ,[PositionTitle]
        ,[Department]
    FROM [dbo].[Badges]
    WHERE ([LastName] = 'Dean')
)
SELECT
     [draw] = 1
    ,[recordsTotal] = COUNT(1)
    ,[recordsFiltered] = (SELECT COUNT(1) FROM [Filtered])
    ,[data] =
    (
        SELECT
             [DT_RowId] = [F].[RowID]
            ,[F].[PositionTitle]
            ,[F].[Department]
        FROM [Filtered] AS [F]
        FOR JSON PATH
    )
FROM [dbo].[Badges] AS [B]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;