JSON array slicing

[aleclarson]

In Postgres, JSON arrays do not support subscript slicing (e.g. array[2:]) syntax.

export function jsonbArraySlice<T>(
  array: SQLExpression<T[]>,
  start: SQLValue<number>,
  end?: SQLValue<number>
) {
  // Postgres array slicing is 1-based and inclusive of both start and
  // end. If end is omitted, slice to the end of the array.
  return sql<T[]>`
    SELECT jsonb_agg(elem)
    FROM (
      SELECT elem
      FROM jsonb_array_elements(${array})
        WITH ORDINALITY arr(elem, ord)
      WHERE ord >= ${start}
      ${end !== undefined ? sql`AND ord <= ${end}` : undefined}
      ORDER BY ord
    ) sub
  `
}

That's a JSONB implementation. Could use a JSON one too. Also need to see if MySQL and SQLite support a similar pattern.