Reading JSON string and find the max value as integer-CodePudding

I have a JSON string as follows:

{"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 following the below query:

DECLARE @json nvarchar(max)
SET @json = '{"value": [
    {
        "AEDAT": "20211110"
    },
    {
        "AEDAT": "20211110"
    },
    {
        "AEDAT": "20211110"
    },
    {
        "AEDAT": "20211112"
    },
    {
        "AEDAT": "20211112"
    },
    {
        "AEDAT": "20211112"
    }
]}';
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?

CodePudding user response：

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

CodePudding user response：

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