SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp(AnalyticGoodsT CHAR(6), date SMALLDATETIME)

INSERT INTO #tmp(AnalyticGoodsT, date)
VALUES ('000001', '20230424'), ('000002', '20230424'), ('000003', '20230424'), ('000004', '20230424')
4 rows affected

SELECT '["' + STRING_AGG( AnalyticGoodsT, '","') + '"]'
FROM #tmp
group by date

(No column name)

["000001","000002","000003","000004"]

SELECT *
FROM (
  SELECT date,
         JSON_QUERY('["' + STRING_AGG( AnalyticGoodsT, '","') + '"]') as bk
          
  FROM #tmp
  group by date
) AS s
FOR JSON PATH
       
JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[{"date":"2023-04-24T00:00:00","bk":["000001","000002","000003","000004"]}]