2

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!

oeter
  • 627
  • 2
  • 8
  • 23
  • Your `"type"` field in both examples is missing its value's closing quote: `"type": "male,`. Those need to be fixed or this is not valid JSON and cannot be used. – Michael Berkowski Jan 08 '21 at 19:49
  • @MichaelBerkowski I wonder how they got those invalid values into the table in the first place. – Barmar Jan 08 '21 at 20:07
  • It may be stored in a TEXT column. If it were a JSON column, it would not allow an invalid JSON document to be inserted. – Bill Karwin Jan 08 '21 at 22:01
  • @Michael Berkowski Just realized I left out the closing quote in the original question but this quote is actually included in database column, I mistyped it, really sorry for misunderstanding caused by this mistake, it has been fixed now. – oeter Jan 09 '21 at 02:57
  • But "Missing a closing quotation" is exactly the error message you would get with a similar error in your data. Examine your full JSON and look for those. – Michael Berkowski Jan 09 '21 at 13:20

0 Answers0