Albertus Brand Venter Albertus Brand Venter - 2 months ago 7
SQL Question

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

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


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
News
,
parties
,
questions
and
Cams
sections

Answer

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' @> '[{
            "ch_id":"33",
            "news":"1",
            "parties":"1",
            "questions ":"1",
            "cam":"1"
        }]';