我的json是
[{"Date":"2022-10-27","Delta":60,"Comment":null},{"Date":"2022-10-26","Delta":60,"Comment":null},{"Date":"2022-10-25","Delta":60,"Comment":null}]
我需要檢查所有日期值是否大于當前時間。如果是，則回傳 true，否則回傳 false。
我試圖做這樣的事情：
SELECT CASE WHEN EXISTS (SELECT * FROM XXX WHERE Dates < GETDATE())
THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT) END
但我需要一個表 XXX，其中包含Dates我的 json 中的所有內容。我如何得到它們？
我什至在想正確的方法來做到這一點，還是有更好的選擇？
uj5u.com熱心網友回復：
DECLARE @json nvarchar(max) = N'[
{"Date":"2022-10-27","Delta":60,"Comment":null},
{"Date":"2022-10-26","Delta":60,"Comment":null},
{"Date":"2022-10-25","Delta":60,"Comment":null}
]';
SELECT CASE WHEN EXISTS
(
SELECT 1 FROM OPENJSON(@json) AS x
CROSS APPLY OPENJSON(value) AS y
WHERE y.[key] = 'Date'
AND TRY_CONVERT(date, y.value, 120) < GETDATE()
) THEN 0 ELSE 1 END;
你可以把它放在一個函式中：
CREATE FUNCTION dbo.SniffOutBadDates
(
@json nvarchar(max),
@key nvarchar(128),
@when datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT AllDatesAreGood = CASE WHEN EXISTS
(
SELECT 1 FROM OPENJSON(@json) AS x
CROSS APPLY OPENJSON(value) AS y
WHERE y.[key] = @key
AND TRY_CONVERT(date, y.value, 120) < @when
) THEN 0 ELSE 1 END
);
然后在一張桌子上呼叫它，如下所示：
SELECT t.id, f.AllDatesAreGood
FROM dbo.YourTableName AS t
CROSS APPLY dbo.SniffOutBadDates
(t.jsonColumn, 'Date', GETDATE()) AS f;
這個小提琴中的作業示例。
uj5u.com熱心網友回復：
如果使用OPENJSON()顯式模式（WITH子句）決議輸入 JSON，則需要一條陳述句：
SELECT IIF(
COUNT(*) = COUNT(CASE WHEN TRY_CONVERT(date, [Date], 23) > GETDATE() THEN 1 END),
1,
0
) AS [Result]
FROM OPENJSON(@json) WITH ([Date] varchar(10))
如果你想使用你當前的宣告：
SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM OPENJSON(@json) WITH ([Date] varchar(10))
WHERE TRY_CONVERT(date, [Date], 23) < GETDATE()
) THEN CAST(0 AS BIT)
ELSE
CAST(1 AS BIT)
END AS Result
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/523360.html