Can’t parse JSON string w/backslash?

The string in the database means you already ‘lost’. Because that looks like JSON but it is not. Had it been correct JSON, It’d have read C:\. Given that you’ve now arrived at a broken location, you can no longer fix it from here – you need to go to whatever made that string appear in the database and fix it there. If you’ve already got millions of such strings in your db and you can’t go back, then you’re in some trouble and need to start thinking about an AI-like rules-based system that tries to figure out what those strings were intended to represent, which is extremely complicated.

If you know for certain that every backslash in these strings is necessarily a real backslash (and not an escape character), which therefore implies no strings in your entire DB could possibly contain an actual quote – because if they do, how are those escaped then? – you could simply execute UPDATE thatTable SET thatColumn = REPLACE(thatColumn, '', '\'); and that’ll fix it. Which still means you need to fully address the problematic situation that caused you your current predicament – so read on either way.

You’d have to paste the code of whatever INSERTed this data if you need help there. A few notes on that:

• Don’t make JSON by just appending characters to a stringbuilder or similar. Use an actual JSON library. Why, you might ask – JSON is so simple, why bother? Well, this – this very question is exhibit A in why it’s a really really bad idea to handroll even seemingly very simple formats. Formats almost invariably have hidden tricky aspects to it, and a library explicitly designed to produce the format in question tends to do it right, where handroll code does not. For java, there’s a ton of libraries you can use – any of those 5 can do the job.

• Some databases, including notably postgres, have native support for JSON columns. They have operators and functions baked in so you can e.g. select all rows with a totalbytes less than 4 billion, or whatever. Storing raw binary data in databases when it is feasible to directly store the underlying data is virtually always a mistake, because one day somebody somewhere designs a feature that requires that you query on the content within that JSON string, and if you then start writing, in DB queries, some sort of SUBSTR/LEFT/RIGHT/~ (regexp) string manipulation you just get stuck further and further, as JSON is far too complex to try to parse in such a fashion. I would very very very strongly reconsider how you are storing this stuff – you should either be storing this in a JSON column instead of a VARCHAR one, or, have a column named ‘MountPoint’, a column named ‘FreeSpace’, and so on (that would be even better). Or, you should be using mangodb or some sort document DB (vs. this, which is a relational DB). Usually that adds up to more trouble than its worth, but it’s definitely vastly superior to storing JSON in VARCHARs in a relational DB – that is the worst of both worlds!