sql - 查询存储在数据库字段中的字符串中的特定值

Question

{"create_channel":"1","update_comm":"1","channels":"*"}

这是我要查询的数据库字段。

如果我想选择所有具有 "create_channel": "1" 的记录，我的查询会是什么样子？和一个 "update_comm": "1"

附加问题:

查看下面的字段:

{"create_channel":"0","update_comm":"0","channels":[{"ch_id":"33","news":"1","parties":"1","questions ":"1","cam":"1","edit":"1","view_subs":"1","invite_subs":"1"},{"ch_id":"18","news":"1","parties":"1","questions ":"1","cam":"1","edit":"1","view_subs":"1","invite_subs":"1"}]}

我将如何找出所有在 News 中的子管理员？ , parties , questions和 Cams部分

Answer 1

您可以使用 the ->> operator以字符串形式返回成员:

select  * 
from    YourTable 
where   YourColumn->>'create_channel' = '1' and
        YourColumn->>'update_comm' = '1'

要查找在 channel 33 中有新闻、聚会、问题和摄像头的用户，您可以使用 the @> operator检查 channel 数组是否包含这些属性:

select  *
from    YourTable
where   YourColumn->'channels' @> '[{
            "ch_id":"33",
            "news":"1",
            "parties":"1",
            "questions ":"1",
            "cam":"1"
        }]';