JSON 嵌套对象到 SQL Server 存储过程 [英] JSON Nested Object to SQL Server Stored Procedure

问题描述

我试图从单个 JSON 对象插入多行，其中每一行是 JSON 嵌套对象中的第 n 个名称/值对

I am trying to insert multiple rows from a single JSON object, where each row is the n'th name/value pair in the JSON nested-object

SQL Server 表如下所示:

Here is what the SQL Server table looks like:

ID | Name | Value |

JSON 如下所示:

{
   "PropData":{ 
    "Name1": "Value1",
    "Name2": "Value2",
    "Name3": "Value3"       
    }
}

这是我到目前为止的存储过程(ID 是静态的，每一行都应该是相同的 - 为了简洁起见，我省略了如何检索它，因为它不是与问题相关).

Here is what I have so far for the stored procedure (ID is static and should be the same for each row - I've left out how that is retrieved for brevity as it isn't relevant to the question).

CREATE PROCEDURE usp.InsertPropData
    @jsonProps NVARCHAR(MAX)
AS
BEGIN 
    SET @ID = (SELECT ID FROM MyOtherTable);

 BEGIN TRANSACTION
     INSERT INTO Prop_Table(ID, Name, Value)
         SELECT @ID, Name, Value
         FROM OPENJSON(@jsonProps)
              WITH(Name VARCHAR(500), Value VARCHAR(500))
     COMMIT TRANSACTION
END

问题是它无法在读取的 JSON 中找到名称和值.我想我需要以某种方式告诉它查看 PropData 嵌套对象并从中解析名称/值?但我不知道如何告诉 SQL 这样做.

The issue is that it's failing to find Name and Value in the JSON read.. I think I need to somehow tell it to look at the PropData nested object and parse the name/value from that? But I do not know how to tell SQL to do that.

这是在这个例子中成功执行存储过程后表应该是什么样子:

Here is what the table should look like after successful execution of the stored procedure in this example:

ID | Name | Value |
---+------+-------+
1  |Name1 | Value1|
1  |Name2 | Value2|
1  |Name3 | Value3|

推荐答案

使用 OPENJSON 并向其传递路径应该可以正常工作.

Using OPENJSON and passing a path to it should work fine.

DECLARE @JSON NVARCHAR(4000) = '{
   "PropData":{ 
    "Name1": "Value1",
    "Name2": "Value2",
    "Name3": "Value3"       
    }
}'


SELECT [key], [value]
FROM OPENJSON(@json, '$.PropData')

这篇关于JSON 嵌套对象到 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！