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

Question

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

Andomar · Accepted Answer · 2016-09-19 08:02:36Z

1

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"
        }]';

edited Sep 19, 2016 at 8:02

answered Sep 19, 2016 at 7:49

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related