Albertus Brand Venter Albertus Brand Venter - 10 months ago 30
SQL Question

Querying for a specific value in a String stored in a database field


This is the database field which I want to query.

What would my query look like if I wanted to select all the records that have a
"create_channel": "1"
and a
"update_comm": "1"

Additional question:

View the field below:

{"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"}]}

How would I go about finding out all those that are subadmins in the

Answer Source

You can use the ->> operator to return a member as a string:

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

To find a user who has news, parties, questions and cam in channel 33, you can use the @> operator to check if the channels array contains those properties:

select  *
from    YourTable
where   YourColumn->'channels' @> '[{
            "questions ":"1",