asked on

Hyphen in JSON data causes OPENJSON to fail on SQL 2016

I'm trying to pull some data from an API with JSON into an SQL 2016 DB. However if the data in one of the fields contains a hyphen it causes the query to fail. How do I solve this?
Thank you
Code:

declare @JSON nvarchar(max) = '[{"id":1,"preferredName":"James","surname":"Brown-Smith"}]'
Merge INTO TblStaff as TARGET 
using(SELECT * FROM OPENJSON(@json) WITH ( 
surname NVARCHAR(MAX) '$.surname', 
preferredName NVARCHAR(MAX) '$.preferredName', 
id INT '$.id' 
)) as SOURCE on TARGET.intStaffID=SOURCE.id when matched then update set TARGET.txtSurname=SOURCE.surname, TARGET.txtPrename=SOURCE.preferredName, TARGET.intStaffID=SOURCE.id 
WHEN NOT MATCHED BY TARGET THEN insert (txtSurname, txtPrename, intStaffID) values (SOURCE.surname,SOURCE.preferredName,SOURCE.id) ;

Open in new window

ste5an

First of all: Post a concise and complete example. What error do you get?

Cause when I craft one it works as intended:

DECLARE @TblStaff TABLE (
    txtSurname NVARCHAR(255) ,
    txtPrename NVARCHAR(255) ,
    intStaffID INT
);

DECLARE @JSON NVARCHAR(MAX) = '[
    {
        "id": 1,
        "preferredName": "James",
        "surname": "Brown-Smith"
    }
]';

MERGE INTO @TblStaff TARGET
USING (   SELECT *
          FROM
                 OPENJSON(@JSON)
                     WITH ( surname NVARCHAR(MAX) '$.surname' ,
                            preferredName NVARCHAR(MAX) '$.preferredName' ,
                            id INT '$.id' )) SOURCE
ON TARGET.intStaffID = SOURCE.id
WHEN MATCHED THEN UPDATE SET TARGET.txtSurname = SOURCE.surname ,
                             TARGET.txtPrename = SOURCE.preferredName ,
                             TARGET.intStaffID = SOURCE.id
WHEN NOT MATCHED BY TARGET THEN INSERT ( txtSurname ,
                                         txtPrename ,
                                         intStaffID )
                                VALUES ( SOURCE.surname, SOURCE.preferredName, SOURCE.id );

SELECT *
FROM   @TblStaff TS;

Open in new window

browsw

ASKER

Thank you, you pointed me towards the problem which was an incorrect data type on the existing table for txtSurname.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.