Does slick-pg support individual ARRAY element access?

[iceberg901]

I am trying to write a query with a condition that would look like this in SQL:

WHERE t1.id = t2.linked_ids[1]

Is there some way to do this with slick-pg without using plain SQL?

I can't find any way to do the direct subscript access with slick-pg. So for example let's say the linked_ids column is defined as

val linkedIds: Column[List[Long]] = column[List[Long]]("linked_ids", O.NotNull, O.Default(List()), O.DBType("BIGINT[]"))

then one cannot use subscript access in scala like:

linkedIds[1]

[tminglei]

No, there isn't. And I can't find a way to implement it yet.

[iceberg901]

Thanks as always for the quick response!

[mdedetrich]

I am also wondering how you would implement this, I don't think individual "index" access for columns is possible (yet) in how slick is set up

[tminglei]

@mdedetrich, currently, I can't find a way to generate WHERE t1.id = t2.linked_ids[1] by using slick.

[mdedetrich]

Yeah thats what I meant, I don't think its physically possible, might have to make an issue at https://github.com/slick/slick for it?

[tminglei]

@mdedetrich yes, let's file an issue to slick.

[alfonsovng]

I suppose that this is not available yet ;(.

[coreycaplan3]

One thing you could do as a solid workaround is make a static function that composes the access. For instance, I wrote the following in Pg SQL:

create or replace function firstElement(any_array anyarray)
  returns anyelement
  immutable
  language plpgsql
as
$$
  begin
    return any_array[1];
  end;
$$;

Then in scala:

def firstElementFn[T: TypedType](array: Rep[List[T]]): Rep[Option[T]] = {
    SimpleFunction.unary[List[T], Option[T]]("firstElement").apply(array)
}

Usage:

table.map { t => 
    val arrayAgg = arrayAgg(t.openPrice.?).sortBy(t.metricTimestamp.asc)
    firstElementFn(arrayAgg) // We pass in the array here!
}

where metricTick is a Query being used in a Group By and t represents the underlying table. Some of the smaller details with the usage may be incorrect, but hopefully a good point was made.