\nRecently, I came across what started as a straightforward problem: In MySQL I was JOINing from Table A to Table B on a’s primary key, and wanted to display a list of all items in Table A, each followed by a list of all associated items in Table B.\n<\/p>\n
\nMy initial thought was to hand-roll my own JSON, so that the result of the query would have a few columns with the results of Table A, then a column with the JSON-encoded data from Table B. I realize that one should never hand-roll their own JSON, but there isn’t a native JSON encoder in MySQL, so I had to make do.\n<\/p>\n
\nIt was easy enough to come up with a basic JSON format using the following. I’ve done some pretty aggressive tabbing and newlines here to try to make the layers of functions as straightforward as possible; different arguments to each function align vertically.\n<\/p>\n
\r\nSELECT a.aId, a.name,\r\n CONCAT(\r\n '[',\r\n GROUP_CONCAT(\r\n CONCAT(\r\n '[',\r\n b.bId, ', ',\r\n '\\\"', b.name, '\\\"',\r\n ']')\r\n ORDER BY b.bId ASC\r\n SEPARATOR ','),\r\n ']'\r\n ) AS bData\r\nFROM a\r\n JOIN b\r\n ON a.bId = b.bId\r\nGROUP BY a.aId\r\n<\/pre>\n\n(Note that I am not the first person to come up with this solution.<\/a>)\n<\/p>\n\nHowever, if b.name<\/code> has a quotation mark (or a variety of other characters), this creates invalid JSON. How do we ensure that we always create valid JSON, even when there are special characters or unicode\/multi-byte characters?\n<\/p>\nThe solution<\/h3>\n
\nEnsuring we always have valid JSON was surprisingly easy – we can hexadecimal-encode<\/a> b.name<\/code> in the MySQL query, which ensures that its character set is 0-9 or A-F, so it will always be properly contained within the quotation marks around it. We then convert it back into a normal string in the application code.\n<\/p>\n\r\nSELECT a.aId, a.name,\r\n CONCAT(\r\n '[',\r\n COALESCE(\r\n GROUP_CONCAT(\r\n CONCAT(\r\n '[',\r\n b.bId, ', ',\r\n '\\\"', HEX(b.name), '\\\"',\r\n ']')\r\n ORDER BY b.bId ASC\r\n SEPARATOR ','),\r\n ''),\r\n ']') AS bData\r\nFROM a\r\n JOIN b\r\n ON a.bId = b.bId\r\nGROUP BY a.aId\r\n<\/pre>\n\nSo let’s say b.name<\/code> was something like '\u00e2\u02dc\u0192'<\/code> (because I know people are always naming their things [unicode Snowman]), the resulting hexadecimal representation is 27E2988327<\/code>. Opaque, but definitely JSON-safe!\n<\/p>\n