Querying records from a table that contain a certain key/value in one of its columns in PostgreSQL-CodePudding

I have the table accounts that stores several account preferences as a hash with key/value pairs in its preferences column.

preferences column stores data in the following way:

{
"accepts_coupons": true,
"accepted_coupon_types": ["percentage", "amount"],
"some_other_preference": [...],
}

How can I select accounts whose accepted_coupon_types contents contain the percentage value?

For example (pseudo code) select * from accounts where accounts.preferences.some_key in ('some_value') ?

I've tried something like this:

select name, preferences from accounts
where preferences in ('percentage')

but I'm getting ERROR: invalid input syntax for type json, Detail: Token "percentage" is invalid.

Is it possible to write a query that will look inside the column key/values to select the accounts that meet a certain value criteria in it's preferences column?

CodePudding user response：

Something like this should work:

    select name, preferences
    from accounts
    where preferences ->> 'accepted_coupon_types' like '%percentage%'

db-fiddle here: https://www.db-fiddle.com/f/cBsWdNZnmEjuaeZXT1yWju/0