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