英文:
How to extract a value from JSON column with MariaDB, being this not an exact value in the JSON field?
问题
我需要从一个JSON字符串中提取一个字段,并在该字段中搜索特定的模式。这个字段只是JSON对象具有的所有属性中的一个属性。我已经阅读了文档,并看到了JSON_EXTRACT
函数。我在数据库方面还是个新手,所以我想在这个问题上得到一些帮助。
{"user_id":"1","status_id":"1","text":"Hello, world"}
假设我想获取数据库表中所有包含"world"的"text"值。我可以使用JSON_EXTRACT
进行提取。但我想要模式,而不是绝对值。
我应该如何做到这一点?
英文:
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?
答案1
得分: 2
您可以使用 json_extract()
提取值,然后使用 like
进行模式匹配:
select t.*
from mytable t
where json_extract(my_json_col, '$.text') like '%world%'
英文:
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%'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论