Doobie query to create a map

Question

Let's say I have some sql that is going to return a result set that looks like this:

ID	Value
A1	Val1
A1	Val2
A1	Val3
B1	Val4
B1	Val5
B1	Val6

val query = sql"""select blah""".query[(ID, VALUE)]
val result: ConnectionIO[(ID, List[VALUE])] = for {
  tuples <- query.to[List]
} yield tuples.traverse(t => t._1 -> t._2)

This is the closest I can get, but I get a compiler error:

Could not find an instance of Applicative for [+T2](ID, T2)

What I want is to turn this into a Map[ID, List[VALUE]]

score 0 · Answer 1 · answered Dec 01 '22 at 10:17

Here, .traverse isn't the most helpful method, try this instead:

val result: ConnectionIO[Map[ID, List[VALUE]]] = for {
  tuples <- query.to[List]
} yield tuples.groupMap(_._1)(_._2)

If you have Scala older than 2.13 you can try:

val result: ConnectionIO[Map[ID, List[VALUE]]] = for {
  tuples <- query.to[List]
} yield tuples
   .groupBy(_._1) // Map[ID, List[(ID, VALUE])]
   .mapValues(_.map(_._2))

score 0 · Answer 2 · answered Dec 01 '22 at 10:26

I don't know what DB you are using, but if you have arrays functions like postgresql you can try to use group by with array_agg. After you can just run .asMap or .groupBy on a List[(ID, List[VALUE])].

val query = 
  sql"""select id, array_agg(value) as values group by id"""
    .query[(ID, List[VALUE])]
val result = query.to[List].map(_.toMap)

Doobie query to create a map

2 Answers2