SQL:Finding max value from a nested JSON in SQL Server-CodePudding

This question has a reference in this SO thread.

I want compute the MAX() value of the below JSON. Also the keys in this are not fixed hence as per the above solution they can't be hardcoded in the code.

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

Also I shall be getting the below string as parameter from some other SP.

DECLARE @ColumnName = 'AEDAT,ERDAT' ---I shall be getting this as an output from another SP.

With the above, my approach is as follows:

DECLARE @col_table TABLE (ID int IDENTITY(1,1), ColumnName varchar(100))
INSERT INTO @col_table SELECT value as ColumnName FROM STRING_SPLIT(@ColumnName,','); 
DECLARE @max_val TABLE (ID int IDENTITY(1,1), LastUpdatedValue int) 
DECLARE @max_ID int
DECLARE @min_ID int
DECLARE @1st_col varchar(max)
SELECT @max_ID = MAX(ID),@min_ID = MIN(ID) FROM @col_table GROUP BY ColumnName;

WHILE (@min_ID <= @max_ID)
  BEGIN
  SELECT @1st_col =  ColumnName FROM @col_table WHERE ID = @min_ID
  INSERT INTO @max_val (LastUpdatedValue)
  SELECT JSON_VALUE(j.value,'$.'  @1st_col  '' ) FROM OPENJSON(@json,'$.value') AS j
  SET @min_ID = @min_ID   1
  END

Ideally the above approach should have given 10 values (5 each for AEDAT and ERDAT) But this is giving me 5 values.

SELECT * FORM @max_val;
 ID  LastUpdatedValue
 1    20211119
 2    20211112
 3    20211123
 4    20211119
 5    20211123

I am expecting output as like UNION ALL of all keys. Like below

 SELECT JSON_VALUE(j.value,'$.AEDAT') FROM OPENJSON(@json,'$.value') AS j
 UNION ALL
 SELECT JSON_VALUE(j.value,'$.ERDAT') FROM OPENJSON(@json,'$.value') AS j

What I am missing out? Please suggest.

CodePudding user response：

You can use OPENJSON twice, once to open the array, and again to unpivot the keys. Then simply filter by your table variable

DECLARE @col_table TABLE (ColumnName varchar(100) COLLATE Latin1_General_BIN2 PRIMARY KEY)
INSERT INTO @col_table (ColumnName)
SELECT value
FROM STRING_SPLIT(@ColumnName,','); 

SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
  j2.[key],
  j2.value
FROM OPENJSON(@json, '$.value') j1
CROSS APPLY OPENJSON(j1.value) j2
WHERE CAST(j2.[key] AS varchar(100)) IN (
    SELECT c.ColumnName
    FROM @col_table c
);

db<>fiddle