這資料是某機台在每月初固定回傳API上個月燈號紀錄~
主管想知道哪幾天機台出現了黑燈?

資料如下：

declare @Machine table(
	MachineYear int
	,MachineMonth int
	,MachineData nvarchar(max)
)

insert into @Machine
values(2022,1,'[{"Date":"2022/1/1","Light":"blue"},{"Date":"2022/1/15","Light":"red"},{"Date":"2022/1/20","Light":"black"},{"Date":"2022/1/26","Light":"blue"}]')
,(2022,2,'[{"Date":"2022/2/3","Light":"blue"},{"Date":"2022/2/11","Light":"blue"},{"Date":"2022/2/16","Light":"black"},{"Date":"2022/2/18","Light":"red"},{"Date":"2022/2/22","Light":"black"}]')

經過SQL查詢後~得知這幾天為黑燈

以上我是在SQL Server 2017版用OpenJson查詢的~
各位試試看自己的方式如何~

我來貼我自己的方式~各位參考看看就好~或許更厲害的~

declare @MachineJson nvarchar(max)
select @MachineJson='[' + Stuff((
	select ',' + Str
	from (
		select Replace(Replace(MachineData,'[',''),']','') Str
		from @Machine
	) k
	for xml path('')
),1,1,'') + ']'

select *
from OpenJson(@MachineJson)
with (
	[Date] date
	,[Light] varchar(max)
)
where [Light] = 'black'

然後在SQL2016以上版本~資料轉Json~或Json轉回資料~的確滿方便的@@"

declare @Tmp table(
	YY int
	,MM int
	,StrChar nvarchar(50)
	,StrDate date
)
insert into @Tmp
values(2022,1,'A','2022/1/3')
,(2022,1,'B','2022/1/13')
,(2022,1,'C','2022/1/25')
,(2022,2,'D','2022/2/11')

declare @MachineJson nvarchar(max)
select @MachineJson=(
	SELECT *
	from @Tmp
	FOR JSON PATH
) 

select @MachineJson JSON

select *
from OpenJson(@MachineJson)
with (
	YY int
	,MM int
	,StrChar nvarchar(50)
	,StrDate date
)