MySQL JSON query ERROR "Missing a closing quotation mark in string"

Question

Edit:

Just realized I left out the closing quote here: {"area": "jp", "type": "female, "tag": {"411": "Doo Wop"}}, I mistyped it in the original question but this quote is actually included in db column, I apologize for misunderstanding caused by this mistake, it has been fixed now.

---

I came across this problem a couple of hours ago, no luck in hours of Google searching.

I'm using MySQL 8.0

There is one column in a table whose data is stored as TEXT in json format:

mysql> select author_class from author limit 1 offset 36273;
+------------------------------------------------------------------------+
| author_class                                                           |
+------------------------------------------------------------------------+
| {"area": "jp", "type": "female", "tag": {"411": "Doo Wop"}}             |
+------------------------------------------------------------------------+

There are other rows whose "tag" field is empty like below:

+------------------------------------------------------------------------+
| author_class                                                           |
+------------------------------------------------------------------------+
| {"area": "ru", "type": "male", "tag": ""}                               |
+------------------------------------------------------------------------+

What I want to do is to select rows whose "tag" field is not empty, like the first example.

I have tried something like this, according to this answer, but the following error occured:

mysql> SELECT author_id, author_class ->> "$.tag" FROM author WHERE author_class ->> "$.tag" IS NOT NULL;
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Missing a closing quotation mark in string." at position 325.

and this didn't work either:

mysql> SELECT author_id, author_class ->> "$.tag" FROM xiami_author WHERE author_class -> "$.tag" IS NOT NULL;
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Missing a closing quotation mark in string." at position 325.

Any help would be appreciated!

Answer 1

等待大神答复