Anonymous 1800
I have a question about this fiddle:
<>https://dbfiddle.uk?rdbms=postgres_10&fiddle=bfa8da4e56716178ffe74ada8a1b489c
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
Truilus
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)
VALUES
('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']
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=83a64c0c261aff4957a88eecdf2b67cf
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']