JSON Nested Object to SQL Server Stored Procedure

Question

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

Here is what the SQL Server table looks like:

ID | Name | Value |

Here is what the JSON looks like:

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

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

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|

SELECT [key], [value] FROM OPENJSON(@jsonProps, '$.PropData'). WITH is good for when you want the data in a structured column form, but that's exactly what you don't need here. — Jeroen Mostert
– Jeroen Mostert, Commented Sep 12, 2018 at 14:06

dfundako · Accepted Answer · 2018-09-12 14:11:36Z

2

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

answered Sep 12, 2018 at 14:11

dfundako

8,3643 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christopher Over a year ago

Thank you - this is what I needed to know (how to tell SQL the path to the sub object).

Collectives™ on Stack Overflow

JSON Nested Object to SQL Server Stored Procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related