In MySql, how do I check if a row with json field has a key that contains a specific text?

Question

So let's say I have a row that looks like

id json_data
1  { ... }
2  { ... }
3  { ... }

I want to select the rows that have a key which contains a string abc. I tried to use something like

SELECT * FROM table1 t where JSON_EXTRACT(t.json_data,...

but wasn't able to complete the query because I wasn't sure how to add this condition.

Any help?

Does this answer your question? [How to retrieve JSON data from MySQL?](https://stackoverflow.com/questions/15701579/how-to-retrieve-json-data-from-mysql) — WOUNDEDStevenJones, Sep 15 '21 at 03:31
No because I am trying to check the Key value, not retrieve the value with the key — Dawn17, Sep 15 '21 at 03:34
Please edit your question to provide a [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) with some example raw data and what your expected result set should be. You have a lot of `...` in your question, which leaves a lot of room for interpretation. — WOUNDEDStevenJones, Sep 15 '21 at 04:02
How much overlap will there be with keys/values matching `abc`? You could treat the column as a raw string and search `WHERE t.json_data LIKE '%abc%'`, and possibly manually filter out false positives. — WOUNDEDStevenJones, Sep 15 '21 at 04:04
Have you tried [JSON_SEARCH()](https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-search)? — Bill Karwin, Sep 15 '21 at 05:03

score 0 · Answer 1 · answered Sep 15 '21 at 05:05

0

If you only need to look at the top level keys, this is simply:

json_keys(json_data) like '%abc%'

answered Sep 15 '21 at 05:05

ysth

1 Answers1