sqljsonsql-servert-sqlopen-json

OPENJSON select value by dynamic key

data row 1 : 
{
    "30":{"status":0,"approval":"0","entrydate":"2023-01-30"},
    "26":{"status":0,"approval":"0","entrydate":"2023-01-30"}
}

data row 2 :
{
    "12":{"status":0,"approval":"0","entrydate":"2023-01-30"},
    "13":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"}
}

data row 3 :
{
    "20":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"},
    "24":{"status":1,"approval":"20022-xxxx","entrydate":"2023-01-30"}
}

How to select row data in a SQL Server database if status=1 => row 2, row 3 and if status=0 => row 1, row 2 because the json key is dynamic.

Solution

  • A possible approach is a combination of OPENJSON() and JSON_VALUE():

    SELECT *
FROM JsonTable
WHERE EXISTS(
   SELECT 1
   FROM OPENJSON(JsonColumn)
   WHERE JSON_VALUE([value], '$.status') = '0'
)