Loading JSON data with apostrophe (single quote) i

Question

This is specifically regarding insert of data into CrateDB,

I have a table with one column of type OBJECT. I am trying to insert JSON data into that using python and it is going along fine.

create table users (userdata OBJECT);

However, my data has apostrophe's in it i.e. single quotes and so the insert is failing. I have replicated the problem on the console:

The problem in the data below is with the "snr" field. I have tried putting in a backslash but that does not help.

Can someone tell me how I can get the following insert to

INSERT into users (userdata) 
VALUES ('{"area_code": "2", "companyname": "TEC", "cos": "National24Hrs-Standard", 
"country_code": "AUS", "cucm_dn": "26902", "ddi": "84236902", "department": null, 
"device_type": "Cisco 8945 SIP", "divisionname": "Demonstrations", 
"emailaddress": "bart.simpson@core.demo.telstra.com", "extension": "26902", 
"extra1": null, "extra2": null, "extra3": null, "extra4": null, 
"featuregroup": "Mobile Worker", "firstname": "Bart", "information": null, 
"ippbxchosen": "TEC-CL-1", "language": "English - United States", "lastname": "Simpson", 
"locationname": "Sydney", "mask": "61284236902", "pickupgroup": null, "postcode": "2000", 
"presence": "Y", "role": "enduser", "security_profile": "EndUser-SP1", 
"snr": "Bart\'s Mobile:0457503561,Bart Simpson:457503561", 
"username": "bart.simpson@core.demo.telstra.com", "voicemail": "UCX-Advanced-CoS"}')

Maybe try two single quotes instead of backslash quote? – kismert May 16 '18 at 20:11 — kismert, May 16 '18 at 20:11
`select 'a''a';` will output `a'a` – Johannes Moser May 17 '18 at 14:11 — Johannes Moser, May 17 '18 at 14:11

score 0 · Answer 1 · answered May 23 '18 at 07:37

Inserting a JSON string into a CrateDB object typed column will always fail, this has afaik nothing to do with wrong escaping in your case. Either insert this as an CrateDB object (which I guess is what you want) to gain the benefits of accessing/searching each key/value, or change the data type to string.

See documentation about using object literals https://crate.io/docs/crate/reference/en/latest/general/ddl/data-types.html#object-literals. Also most CrateDB client drivers do support binding data as objects.

Loading JSON data with apostrophe (single quote) i

1 Answers1