get the column name by given value in postgres

Question

I have the table geo with fields 'us','uk','india','china' I want to get the column name which has the value '1'

If I give the sno 1 I want to get india which has the value 1 and for sno 2 I want to get us and china How to get the column name by the value and sno.

Answer 1

This sounds like a strange database design, but can be achieved by converting the row to a JSON value. With that it's possible to iterate over the column names and pick those that have the value 1

select (select string_agg(col, ', ' order by col) 
        from jsonb_each_text(to_jsonb(geo) - 'sno') t(col, val)
        where t.val = '1')
from geo
where sno = ...;

Online example