Slick-pg: How to use arrayElementsText and overlap operator "?|"?

615 Views Asked by At

I'm trying to write the following query in scala using slick/slick-pg, but I don't have much experience with slick and can't figure out how:

SELECT * 
FROM attributes a 
WHERE a.other_id = 10 
      and ARRAY(SELECT jsonb_array_elements_text(a.value->'value'))
          && array['1','30','205'];

This is a simplified version of the attributes table, where the value field is a jsonb:

class Attributes(tag: Tag) extends Table[Attribute](tag, "ship_attributes") {
    def id = column[Int]("id")
    def other_id = column[Int]("other_id")
    def value = column[Json]("value")

    def * = (id, other_id, value) <> (Attribute.tupled, Attribute.unapply)

}

Sample data:

|  id  |  other_id  |  value                                    |
|:-----|:-----------|:------------------------------------------|
|  1   |  10        | {"type": "IdList", "value": [1, 21]}      | 
|  2   |  10        | {"type": "IdList", "value": [5, 30]}      | 
|  3   |  10        | {"type": "IdList", "value": [7, 36]}      | 

This is my current query:

attributes
    .filter(_.other_id = 10)
    .filter { a => 
        val innerQuery = attributes.map { _ => 
            a.+>"value".arrayElementsText
        }.to[List]
        innerQuery @& List("1", "30", "205").bind
    }

But it's complaining about the .to[List] conversion.

I've tried to create a SimpleFunction.unary[X, List[String]]("ARRAY"), but I don't know how to pass innerQuery to it (innerQuery is Query[Rep[String], String, Seq]).

Any ideas are very much appreciated.

UPDATE 1

while I can't figure this out, I changed the app to save in the database the json field as a list of strings instead of integer to be able to do this simple query:

attributes
    .filter(_.other_id = 10)
    .filter(_.+>"value" ?| List("1", "30", "205").bind)


|  id  |  other_id  |  value                                    |
|:-----|:-----------|:------------------------------------------|
|  1   |  10        | {"type": "IdList", "value": ["1", "21"]}  | 
|  2   |  10        | {"type": "IdList", "value": ["5", "30"]}  | 
|  3   |  10        | {"type": "IdList", "value": ["7", "36"]}  | 
0

There are 0 best solutions below