How to check all values passed by dictionary list in json?-CodePudding

My json is

[{"Date":"2022-10-27","Delta":60,"Comment":null},{"Date":"2022-10-26","Delta":60,"Comment":null},{"Date":"2022-10-25","Delta":60,"Comment":null}]

I need to check that all Date values are greater than the current time. If so, return true, otherwise false.

I tried to do something like this:

SELECT CASE WHEN EXISTS (SELECT * FROM XXX WHERE Dates < GETDATE())
THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT) END

But I need a table XXX with all the Dates from my json. How do I get them?

And am I even thinking the right way to do this, or is there a better option?

CodePudding user response：

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;

You can put this in a function:

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
  );

Then call it against a table, like this:

SELECT t.id, f.AllDatesAreGood
  FROM dbo.YourTableName AS t
  CROSS APPLY dbo.SniffOutBadDates
              (t.jsonColumn, 'Date', GETDATE()) AS f;

Working example in this fiddle.

CodePudding user response：

If you parse the input JSON using OPENJSON() with explicit schema you need a single statement:

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))