sql - How to extract a value from JSON column with MariaDB, being this not an exact value in the JSON field?

Question

I need to extract a field from a JSON string with MariaDB and search for specific patterns in that field.

This field is just a property of all the properties the JSON object has. I had read the documentation and I saw the JSON_EXTRACT function. I am still a newbie with databases so I would like some help in this matter.

{"user_id":"1","status_id":"1","text":"Hello, world"}

Lets say I want to get all the "text" values that have the "world" in the database table. I can extract with JSON_EXTRACT. But I want patterns, not absolute values.

How can I do that?

标签： sqljsondatabasemariadb

Answer 1

You can extract the value with json_extract(), and then do pattern matching with like:

select t.*
from mytable t
where json_extract(my_json_col, '$.text') like '%world%'