Reading JSON string and find the max value as integer

Question

I have a JSON string as follows:

DECLARE @json nvarchar(max)
SET @json = '{"value": [
    {
        "AEDAT": "20211110"
    },
    {
        "AEDAT": "20211110"
    },
    {
        "AEDAT": "20211110"
    },
    {
        "AEDAT": "20211112"
    },
    {
        "AEDAT": "20211112"
    },
    {
        "AEDAT": "20211112"
    }
]}';

Now I want to read this JSON in SQL Server using OPENJSON() and find the MAX value for each AEDAT. For this, I am using the following query:

SELECT MAX(value)
FROM OPENJSON(@json, '$.value')

The above query is returning a row with key value pair as below:

{"AEDAT":"20211112"}

My objective is to get only 20211112 as integer.

How to achieve this?

Answer 1

If you want to get the max value as integer, you need to use OPENJSON() with explicit schema (the WITH clause with columns definitions). This schema depends on the structure of the parsed JSON (in your case it's a JSON array):

SELECT MAX(AEDAT) AS MaxAEDAT
FROM OPENJSON(@json, '$.value') WITH (
   AEDAT int '$.AEDAT'   
)

If the parsed values are dates, you may try a different statement:

SELECT MAX(TRY_CONVERT(date, AEDAT, 112)) AS MaxAEDAT
FROM OPENJSON(@json, '$.value') WITH (
   AEDAT varchar(8) '$.AEDAT'   
)

Answer 2

OPENJSON without explicit schema, gives you the value column which, in your example, will contain an object such as {"AEDAT": "20211110"} having type = 5. Use JSON_VALUE on that object:

select max(cast(json_value(j.value, '$.AEDAT') as int))
from openjson(@json, '$.value') as j