SQL Server OPENJSON通过动态键选择值

roejwanj 于 2023-02-15 发布在其他

关注(0)|答案(1)|浏览(143)

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"}
}

如何在status=1 =〉row 2，row 3以及status=0 =〉row 1，row 2的情况下选择SQL Server数据库中的行数据，因为json键是动态的。

sql-server

来源：https://stackoverflow.com/questions/75373113/openjson-select-value-by-dynamic-key

1条答案

按热度按时间

ktecyv1j1#

一种可能的方法是OPENJSON()和JSON_VALUE()的组合：

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

赞(0）回复(0）举报 2023-02-15

我来回答

SQL Server OPENJSON通过动态键选择值

1条答案

相关问题

热门标签

最新问答