CODEWITHSUNDEEP
sqljsonsql-servert-sqlopen-json
Irvan Affandy
Irvan Affandy

Reputation: 41

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.

Upvotes: 0

Views: 148

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 1

Related Questions