amazon-redshift

Query super in redshift for values with a forward slash

In redshift I have data in a super type column roles like


{"foo":true, "foo/bar":true}

I can query for foo like

WHERE roles.foo = true

I cannot work out how to escape to query for foo/bar. I get the following errors

WHERE roles.foo/bar = true // ERROR: column "bar" does not exist 
WHERE "roles.foo/bar" = true // ERROR: column "roles.foo/bar" does not exist
WHERE roles."foo/bar" = true // no rows found, but there should be
WHERE roles.`foo/bar` = true // syntax error
WHERE `roles.foo/bar` = true // ERROR: column "bar" does not exist

Different types of escaping the forward slash do not work either. Can anyone help? Thank you.

Solution

  • My colleague gave me the answer

        FROM tab t, UNPIVOT t.roles AS has_role AT role
    WHERE role like 'foo%' AND has_role = true