Search code examples
mysqlmysql-json

Extract value by key with double quote and backslash in json column

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?

Solution

  • 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.