syntax error during INSERT statement with JSON-CodePudding

I have trouble to find the correct syntax to store JSON in a SQL table. The table looks like below, id will increment automatically :

I receive an error that my syntax is wrong near '$.nodeid' but I assumed I followed the official guides. Obvously I am wrong.

BEGIN TRANSACTION [insert]
  BEGIN TRY
  DECLARE @properties nvarchar(max) = N'{"nodeid": 306, "eigenschaft": "Test ", "wert": "Test "},
                                        {"nodeid": 306, "eigenschaft": "Test2", "wert": "Test2"},
                                        {"nodeid": 306, "eigenschaft": "Test3", "wert": "Test3"}';
  INSERT INTO dbo.eigenschaften
  SELECT *
  FROM OPENJSON(@properties)
  WITH ( nodeid int, '$.nodeid',
         eigenschaft varchar(50), '$.eigenschaft',
         wert varchar(50), '$.wert',
         properties nvarchar(max) '$' AS JSON );
  COMMIT TRANSACTION [insert]
  END TRY
  BEGIN CATCH
  ROLLBACK TRANSACTION [insert]
  END CATCH;

CodePudding user response：

If you expect 3 rows in the output @properties should be a JSON array

DECLARE @properties nvarchar(max) = N'[{"nodeid": 306, "eigenschaft": "Test ", "wert": "Test "},
                                    {"nodeid": 306, "eigenschaft": "Test2", "wert": "Test2"},
                                    {"nodeid": 306, "eigenschaft": "Test3", "wert": "Test3"}]';

Next, the WITH clause accepts no comma between the column definition and the JSON path

SELECT *
FROM OPENJSON(@properties)
WITH ( nodeid int '$.nodeid',
     eigenschaft varchar(50) '$.eigenschaft',
     wert varchar(50) '$.wert',
     properties nvarchar(max) '$' AS JSON );

Returns

nodeid  eigenschaft wert    properties
306 Test    Test    {"nodeid": 306, "eigenschaft": "Test ", "wert": "Test "}
306 Test2   Test2   {"nodeid": 306, "eigenschaft": "Test2", "wert": "Test2"}
306 Test3   Test3   {"nodeid": 306, "eigenschaft": "Test3", "wert": "Test3"}

db<>fiddle