如何从sql server中的表中查询json数组类型列中的值？

I have a table with a single row of data which has a nvarchar(MAX) type column, lets name the column as json_column and the values of the column are like below:

我有一个包含单行数据的表,它有一个nvarchar(MAX)类型的列,让我们将列命名为json_column,列的值如下所示:

{
"data": [
    {
        "name": "john",
        "num": "123"
    },
    {
        "name": "peter",
        "num": "345"
    },
    {
        "name": "donald",
        "num": "789"
    },
 ]
}

Now how can I get the value of num where name is passed as input to a where clause or query?

现在我如何获取num的值,其中name作为输入传递给where子句或查询?

My query should return only the value of num.

我的查询应该只返回num的值。

For example: lets say I need the value of num where name is 'john' and the query should return me 123.

例如:假设我需要num的值,其中name是'john',查询应该返回123。

1 个解决方案

#1

I just found the answer and the query can be written as follows:

我刚刚找到了答案,查询可以写成如下:

SELECT OPENJSON((SELECT json_column From json_column_table),'$.data')
WITH 
(
 name nvarchar(100) '$.name',
 num  nvarchar(100) '$.num'
)
WHERE name='john';