1

I have a table include data like this

| id | jdoc                                |
+----+-------------------------------------------+
|  1 | {"simple": "val1", "h\"simple": "val2"}   |
|  1 | {"simple": "val1", "h\"simple": "val2"}   |
|  1 | {"simple": "val1", "h\\\"simple": "val2"} |
+----+-------------------------------------------+

the jdoc is the column in json format and I am trying to fetch values by key.

I can fetch value with key simple by run the query select jdoc->'$."simple"' from test2 ;

But similar query does not work for retrieving the values for key "h\"simple" For example, query select jdoc->'$."h\"simple"' from test2 ; return a list of nulls, which clearly shows the query does not hit the right key.

| jdoc->'$."h\"simple"' |
+-----------------------------+
| NULL                        |
| NULL                        |
| NULL                        |
+-----------------------------+

Additionally, I tried query with escapging for double quote and backslash based on: https://dev.mysql.com/doc/refman/8.0/en/string-literals.html.

select jdoc->'$."h\\\"simple"' from test2 ;

but it complains ERROR: 3143 (42000): Invalid JSON path expression. The error is around character position 13.

Any suggestion about how to query such keys with special characters like double quote and backslash?

dashenswen
  • 540
  • 2
  • 4
  • 21

1 Answers1

1

Here's a test:

mysql> select json_extract(cast('{"simple": "val1", "h\\"simple": "val2"}' as json),
    '$."h\\"simple"') as h_simple;
+----------+
| h_simple |
+----------+
| "val2"   |
+----------+

You need a literal \ in the JSON path expression so it escapes the " to JSON. But MySQL strings treat backslash as special, so you have to use a double-backslash to ensure that a single literal backslash makes it past MySQL's string literal parser.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • can u elaborate on why we need double backslash? a link would be appreciated. since from https://dev.mysql.com/doc/refman/8.0/en/string-literals.html, it looks like I just need single backslash to escape the double quote and another one to escape the backslash, which should be `'$."h\\\*simple"'`. The key only contains one backslash and one double quote `{"simple": "val1", "h\"simple": "val2"}` – dashenswen May 07 '21 at 00:28
  • I notice u store `'{"simple": "val1", "h\\"simple": "val2"}'` in the example posted, which contains double quotes? – dashenswen May 07 '21 at 00:31
  • I already explained it in my answer above. – Bill Karwin May 07 '21 at 01:07
  • read your answer again and I think my confusion is why '`{"simple": "val1", "h\\"simple": "val2"}'` is stored as `{"simple": "val1", "h\"simple": "val2"}`. I would expect to see only double quote is stored if double-backslash to ensure that a single literal backslash in mysql – dashenswen May 07 '21 at 03:34
  • There has to be a single literal backslash in the string stored in the database, because that will escape the literal double-quote. Otherwise a double-quote is the termination of a JSON string value. – Bill Karwin May 07 '21 at 03:39