Scala, Doobie, PostgreSQL - how to select from array/jsonb column?

Question

I have a simple db table:

create table if not exists players
(
  id bigint,
  name text,
  results text[]
);

Now I would like to create select query, where I want only rows with passed results. So I created a scala code with doobie:

def getPlayers(id: Int, result: String): Query[Int] = {
  sql"select id from players where results ? $result".query[Int]
}

But it didn't work as expected. My question is how to select from array column in postgresql? Currently I have results as an array, but I could change it to jsonb if it is easier.

Answer 1

You can use the following query:

select id from players where $result = any(results);

You can find more information here:

https://www.postgresql.org/docs/current/functions-comparisons.html