I have json string as

DECLARE @json nvarchar(max)
SET @json = '{"value": [
    {
        "AEDAT": "20211119",
        "ERDAT": "20211119"
    },
    {
        "AEDAT": "20211119",
        "ERDAT": "20211112"
    },
    {
        "AEDAT": "20211123",
        "ERDAT": "20211123"
    },
    {
        "AEDAT": "00000000",
        "ERDAT": "20211119"
    },
    {
        "AEDAT": "00000000",
        "ERDAT": "20211123"
    }]}'

And then when I use OPENJSON then (only 1st two rows) :

key         value                                 type
 0    {"AEDAT": "20211119", "ERDAT": "20211119"}    5
 1    { "AEDAT": "20211119", "ERDAT": "20211112"}   5

I want to MAX value of the value column. So in the above example the result should be 20211119.

Based on here1 and as suggested by someone, I am getting the error as

Conversion failed when converting the nvarchar value '{"AEDAT": "20211119", "ERDAT": "20211119"}' to data type int.

Any clue on how to achieve the same? I am novice to SQL Server hence asking for help.

CodePudding user response：

Please try the following solution.

It calculates the max value is two steps:

1. For each row.
2. Global single max value across all rows for the entire dataset.

SQL

DECLARE @j NVARCHAR(MAX) = 
N'{"value": [
    {
        "AEDAT": "20211119",
        "ERDAT": "20211119"
    },
    {
        "AEDAT": "20211119",
        "ERDAT": "20211112"
    },
    {
        "AEDAT": "20211123",
        "ERDAT": "20211123"
    },
    {
        "AEDAT": "00000000",
        "ERDAT": "20211119"
    },
    {
        "AEDAT": "00000000",
        "ERDAT": "20211123"
    }]}';

WITH rs AS
(
    SELECT *
    , (SELECT MAX(val) FROM (VALUES (AEDAT), (ERDAT)) AS v(val)) AS MaxValue
    FROM OPENJSON(@j,'$.value') WITH (
          AEDAT int '$.AEDAT',
          ERDAT int '$.ERDAT'
       )
)
SELECT MAX(rs.MaxValue) AS result
FROM rs;

Output

 ---------- 
|  result  |
 ---------- 
| 20211123 |
 ----------