postgresql add tag
Anonymous 1800
I have a question about this fiddle:


My table has a jsonb 'attributes' column where 'owning_group' key has a comma-separated string. 
I have a case where I need to update the 'domain' column where the 'owning_group' value contains any value that is inside a given array.

I something but now I'm just lost. Here's hoping someone can help out.

Top Answer
The expression `attributes->>'owning_groups'`     
returns a single (comma separated) string: `PES_ROLE,OG_STANDARD_TOR,VIEW_PUBLIC_CASE`

And that string does not match any of the strings in your IN list (the IN operator does not know anything about comma separated strings) 

If you want to store a list of values, you should use a proper array: 

    INSERT INTO reports 
      (userid, attributes)
      ('nraka', '{"owning_groups":["PES_ROLE","OG_STANDARD_TOR","VIEW_PUBLIC_CASE"], "locale":"en"}');

Then you can test the array if it contains any of the list of values using the `?|` operator:

    SELECT userid, attributes->>'owning_groups' 
    FROM reports
    WHERE attributes -> 'owning_groups' ?| array['OG_STANDARD_TOR','OG_STANDARD_MTL','OG_EXCEPTION_TOR','OG_EXCEPTION_MTL']
This will now properly return the row in the table. So you can use that operator in the WHERE clause for your update: 

    update reports
      set domain = 'PERCOM' 
    WHERE attributes -> 'owning_groups' ?| array['OG_STANDARD_TOR','OG_STANDARD_MTL','OG_EXCEPTION_TOR','OG_EXCEPTION_MTL']

If for some reason you can't change the comma separated string into a proper JSON array you will need to convert it in the WHERE clause:

    WHERE string_to_array(attributes ->> 'owning_groups', ',') && array['OG_STANDARD_TOR','OG_STANDARD_MTL','OG_EXCEPTION_TOR','OG_EXCEPTION_MTL']

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.