1

I'm converting an SQL query to JSON using something like:

SELECT JSON_ARRAYAGG(JSON_OBJECT('field1', field1, ...)) FROM db.table

One of my columns of string type contain simple ASCII strings with double quotes (") here and there.

My problem is that the double quotes are converted to \\", which is invalid JSON.

How can I make sure that \" is generated instead?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
meaning-matters
  • 21,929
  • 10
  • 82
  • 142
  • I tested on MySQL 5.7 and got: `[{"field1": "\"myfield\""}]`. Cannot reproduce what you saw. What version of MySQL are you using? – Bill Karwin Dec 18 '20 at 22:02
  • @BillKarwin I have `mysql Ver 15.1 Distrib 10.5.8-MariaDB`. – meaning-matters Dec 18 '20 at 22:25
  • 1
    Ah, you have the mysql client (v15.1) but the server is MariaDB, not MySQL. Their implementation of JSON is totally different from MySQL, so my test is not relevant. I don't use MariaDB, I consider it a completely different database product. – Bill Karwin Dec 18 '20 at 23:03
  • 1
    Can't reproduce that either on MariaDB 10.5.8: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=977a0c18e5c5b7e92e6babeefba8413e. Maybe your client is escaping the JSON string, not the database... – GMB Dec 18 '20 at 23:21
  • @GMB Thanks for checking. I run `mysql ... > MyFile.json` from the command line. – meaning-matters Dec 19 '20 at 06:18

0 Answers0