TSQL problem with FOR JSON PATH and single quotes

Question

I have a problem converting results from a SELECT statement to Json where some of the data has single quotes. Here's the scenario:

1) This is the data I'm using which I will provide below:

2) When I run this straight from the database the json results end as shown in bubble #1

3) When I reproduce this using the code below the json results end as shown in bubble #3
Same problem but it just breaks on a different row (don't know why but maybe it doesn't matter).

4) As you can see the data has 4 rows with a single quote as shown below.

5) I tried wrapping the text in STRING_ESCAPE but that didn't' help. Below is everything you need to create a test table, load it with the data shown in the screenshots and the SELECT with "FOR JSON PATH' . Note: I ecsaped the single quotes with an extra single quote to make the insert statement work.

CREATE TABLE zzTestJson (Id uniqueidentifier, CompanyName varchar(100), Type varchar(10))  
INSERT INTO zzTestJson (Id, CompanyName, Type)  
Values  
('1A0F9762-E60A-4792-9BC1-9478182AB0A0', 'Courier & Delivery Service', 'Cat')  
, ('0CA870E1-3DF5-4894-85A2-03339216D757', 'Delivery Service', 'Cat')  
, ('099DB223-7B6A-4B98-95C7-FFEA121E7D6F', 'Adams Warehouse & Delivery Inc,  3300 W 11th St', 'Company')  
, ('35FF2AF6-3634-4D74-90D9-AD15B5DFF296', 'Aew Delivery,  PO Box 431433', 'Company')  
, ('3283C774-74A0-46F0-B3D9-0F423D0E2233', 'Baltodano Moving & Delivering,  6631 Mayfair', 'Company')  
, ('E4A5AD1F-C2F5-46F4-B422-CD354180AD44', 'Baltodano Moving & Delivery,  1234 Silber Rd', 'Company')  
, ('0F8A43AE-D3AF-4479-8B90-596A1EC519EE', 'Baltodano Moving & Delivery,  7811 N Shepherd Dr Ste 201', 'Company')  
, ('862F2F3B-8EAC-49A1-83C1-5508DB08E353', 'Coast 2 Coast Moving & Delivery,  13455 Knightsway Dr', 'Company')  
, ('F500E10C-FED8-464A-9DDC-F055EDADCFCE', 'Coast 2 Coast Moving & Delivery,  9909 Harwin Dr', 'Company')  
, ('84B34920-D5EB-4B70-B581-294BA8ED2401', 'Dellcoat Freight Services,  3800 Garrow St', 'Company')  
, ('55419A68-F6E9-4B63-ADF4-264914A7494A', 'Designers Choice Delivery,  917 1/2 Bennington St', 'Company')  
, ('8D570E22-1BBD-44E5-9C9E-B56011AE97C1', 'Diligent Delivery Systems,  333 N Sam Houston Pkwy E Ste 1000', 'Company')  
, ('3373F520-28CF-4F49-8E64-874F4C833FF8', 'E & M Delivery,  8500 Market St Ste A', 'Company')  
, ('B94AD805-5526-4642-A6CD-12A6C8733F46', 'Exclusive Moving & Delivery,  12602 Mews Cir Apt D', 'Company')  
, ('0FA6B7B3-9973-4BAE-9F49-63674268DE86', 'Hackbarth Delivery Service,  1980 Afton St', 'Company')  
, ('5EA6D8A4-AA36-4DBC-B515-AA2572E94633', 'Heroes Delivery & Moving, LLC,  6116 Skyline Dr', 'Company')  
, ('73C2AFD2-CAF4-4689-92D3-0E2A7A532D90', 'Jc & L Delivery,  5450 Gulfton St', 'Company')  
, ('1129CB04-833A-42F8-BCF6-F551FF2A88AC', 'Lalos Delivers & Movers,  9803 Harwin Dr', 'Company')  
, ('D17B5E8D-67F9-4BA3-84BD-C1968BECB2C6', 'Marvin''s Moving & Delivery,  12311 Split Rail Ln', 'Company')  
, ('9766E80D-CEF7-4A4D-AC0A-06DD0C711D15', 'Marvin''s Moving and Delivery,  12311 Split Rail Ln', 'Company')  
, ('525C792E-8608-4E7C-A386-11EA244CC382', 'Mover''s & Delivery Services of Houston,  10046 Chickasaw Ln', 'Company')  
, ('DF9A9006-888E-4C11-8905-E45A723FBC16', 'Ontime Movers and Delivery,  11917 Bob White #87', 'Company')  
, ('BE6D052C-41F6-463B-A6C0-CECC93CADA64', 'PICKUP: Instant Delivery,  1121 Delano St', 'Company')  
, ('23904238-DD97-4053-B0BA-CA3FD9299792', 'Southwest Moving and Delivery,  10403 Rockley Rd', 'Company')  
, ('A77F55E0-EB98-4DF0-B633-E73EE2C4F3AF', 'TDR''s Moving & Delivery Service,  13030 Northborough Dr', 'Company')  


--Test 1  
SELECT Id, CompanyName, Type   
FROM zzTestJson  
FOR JSON PATH  

--Test 2  
SELECT Id, STRING_ESCAPE(CompanyName, 'JSON') AS CompanyName, Type   
FROM zzTestJson  
FOR JSON PATH  

Can someone kindly please tell me how to get this right?
Thank You!

Answer 1

I think there is a bug of the parser on the & sign. You might try this:

DECLARE @json nvarchar(max);
SET @json = (
    SELECT Id, CompanyName, Type 
    FROM zzTestJson
    FOR JSON PATH
);

SELECT @json;

You are not able to click on the output to get the JSON string, but if you paste it on the Notepad, you should see the string:

[{"Id":"1A0F9762-E60A-4792-9BC1-9478182AB0A0","CompanyName":"Courier & Delivery Service","Type":"Cat"},{"Id":"0CA870E1-3DF5-4894-85A2-03339216D757","CompanyName":"Delivery Service","Type":"Cat"},{"Id":"099DB223-7B6A-4B98-95C7-FFEA121E7D6F","CompanyName":"Adams Warehouse & Delivery Inc, 3300 W 11th St","Type":"Company"},{"Id":"35FF2AF6-3634-4D74-90D9-AD15B5DFF296","CompanyName":"Aew Delivery, PO Box 431433","Type":"Company"},{"Id":"3283C774-74A0-46F0-B3D9-0F423D0E2233","CompanyName":"Baltodano Moving & Delivering, 6631 Mayfair","Type":"Company"},{"Id":"E4A5AD1F-C2F5-46F4-B422-CD354180AD44","CompanyName":"Baltodano Moving & Delivery, 1234 Silber Rd","Type":"Company"},{"Id":"0F8A43AE-D3AF-4479-8B90-596A1EC519EE","CompanyName":"Baltodano Moving & Delivery, 7811 N Shepherd Dr Ste 201","Type":"Company"},{"Id":"862F2F3B-8EAC-49A1-83C1-5508DB08E353","CompanyName":"Coast 2 Coast Moving & Delivery, 13455 Knightsway Dr","Type":"Company"},{"Id":"F500E10C-FED8-464A-9DDC-F055EDADCFCE","CompanyName":"Coast 2 Coast Moving & Delivery, 9909 Harwin Dr","Type":"Company"},{"Id":"84B34920-D5EB-4B70-B581-294BA8ED2401","CompanyName":"Dellcoat Freight Services, 3800 Garrow St","Type":"Company"},{"Id":"55419A68-F6E9-4B63-ADF4-264914A7494A","CompanyName":"Designers Choice Delivery, 917 1/2 Bennington St","Type":"Company"},{"Id":"8D570E22-1BBD-44E5-9C9E-B56011AE97C1","CompanyName":"Diligent Delivery Systems, 333 N Sam Houston Pkwy E Ste 1000","Type":"Company"},{"Id":"3373F520-28CF-4F49-8E64-874F4C833FF8","CompanyName":"E & M Delivery, 8500 Market St Ste A","Type":"Company"},{"Id":"B94AD805-5526-4642-A6CD-12A6C8733F46","CompanyName":"Exclusive Moving & Delivery, 12602 Mews Cir Apt D","Type":"Company"},{"Id":"0FA6B7B3-9973-4BAE-9F49-63674268DE86","CompanyName":"Hackbarth Delivery Service, 1980 Afton St","Type":"Company"},{"Id":"5EA6D8A4-AA36-4DBC-B515-AA2572E94633","CompanyName":"Heroes Delivery & Moving, LLC, 6116 Skyline Dr","Type":"Company"},{"Id":"73C2AFD2-CAF4-4689-92D3-0E2A7A532D90","CompanyName":"Jc & L Delivery, 5450 Gulfton St","Type":"Company"},{"Id":"1129CB04-833A-42F8-BCF6-F551FF2A88AC","CompanyName":"Lalos Delivers & Movers, 9803 Harwin Dr","Type":"Company"},{"Id":"D17B5E8D-67F9-4BA3-84BD-C1968BECB2C6","CompanyName":"Marvin's Moving & Delivery, 12311 Split Rail Ln","Type":"Company"},{"Id":"9766E80D-CEF7-4A4D-AC0A-06DD0C711D15","CompanyName":"Marvin's Moving and Delivery, 12311 Split Rail Ln","Type":"Company"},{"Id":"525C792E-8608-4E7C-A386-11EA244CC382","CompanyName":"Mover's & Delivery Services of Houston, 10046 Chickasaw Ln","Type":"Company"},{"Id":"DF9A9006-888E-4C11-8905-E45A723FBC16","CompanyName":"Ontime Movers and Delivery, 11917 Bob White #87","Type":"Company"},{"Id":"BE6D052C-41F6-463B-A6C0-CECC93CADA64","CompanyName":"PICKUP: Instant Delivery, 1121 Delano St","Type":"Company"},{"Id":"23904238-DD97-4053-B0BA-CA3FD9299792","CompanyName":"Southwest Moving and Delivery, 10403 Rockley Rd","Type":"Company"},{"Id":"A77F55E0-EB98-4DF0-B633-E73EE2C4F3AF","CompanyName":"TDR's Moving & Delivery Service, 13030 Northborough Dr","Type":"Company"}]

Answer 2

I'm afraid that I don't understand what the problem is. I did not go through the JSON in details, but the Marvin's names, those with the single quotes, seems to come out fine, see the attached screen shot.

Possibly the problem is that you are running the query in SSMS;,and SSMS does not have proper support for JSON. For JSON queries, you need to use Azure Data Studio. If you have SSMS 18.7.1 installed, you already have ADS installed, as they bundle ADS with SSMS these days.