Create a stored procedure, something like-

CREATE PROCEDURE GetBulkData
AS
BEGIN
  SELECT JSON_VALUE(a.Data, '$.BatchId') BatchID,
    COUNT(JSON_VALUE(a.Data, '$.BatchId')) AS BatchIDCount,
    a.Name
  FROM Table1 a
  GROUP BY JSON_VALUE(a.Data, '$.BatchId'), a.Name
  HAVING COUNT(JSON_VALUE(a.Data, '$.BatchId')) > 1;
END;

Execute this sp using - EXEC GetBulkData;

You didn't specify what exactly you needed help with. I'm assuming you're struggling with how to do the aggregation where the grouped row only has values from the earliest row in the group.

You need to use a window function and sort the rows by batch ID.

with sorted_batches as (
  select
    t.RecordID,
    t.Timestamp,
    t.Data,
    t.Category,
    JSON_VALUE(t.Data, '$.Name') as Name,
    JSON_VALUE(t.Data, '$.BatchId') as BatchID,
    row_number() over(partition by JSON_VALUE(t.Data, '$.BatchId'), order by t.Timestamp asc) as position_in_batch,
    count(*) over (partition by JSON_VALUE(t.Data, '$.BatchId')) AS record_count_in_batch
  from table1 t
)
select
  bt.Timestamp,
  json_object('RecordId': bt.RecordID, 'Name': bt.Name, 'Count', bt.record_count_in_batch) as Data,
  bt.Category,
from sorted_batches bt
where bt.position_in_batch = 1;

I used the json_object function to stitch the fields together into a JSON document. I used the count and row_number window functions to do the aggregation.