We are able to select the first element of a column of type array in Postgres SQL database. But I'm not able to query the same using knex.

I have tried this.

database('items')
    .select({icon: 'images[0]})
    .then(data => {
    res.send(data) 
}

Expecting the first element of images column of items table.

We are able to select the first element of a column of type array in Postgres SQL database. But I'm not able to query the same using knex.

I have tried this.

database('items')
    .select({icon: 'images[0]})
    .then(data => {
    res.send(data) 
}

Expecting the first element of images column of items table.

• javascript
• arrays
• knex.js

• Is icon column is of a type json? – felixmosh Commented May 26, 2019 at 10:43
• An icon is not the column Images is a column of type text[] @felixmosh – Pratap Sharma Commented May 26, 2019 at 15:15
• How it will look like in regular SQL string, without Knex? – felixmosh Commented May 26, 2019 at 20:03

2 Answers 2

Try using the first() function. It returns the first row in your table (in whatever order the table is sorted). The .select('images') will limit the columns returned to just images.

knex
  .select('images')
  .table('items')
  .first()
  .then((data) => {
    // first row of 'images' is an array.
    // return only the first item in array.
    res.send(data[0]);
})

https://www.postgresql/docs/current/arrays.html#ARRAYS-ACCESSING

This seems to build correct kind of query for it in knex (notice that in postgresql arrays starts from index 1):

knex('items').select({icon: knex.raw('??[1]', ['images'])})

Here is runkit example of generated query https://runkit./embed/1apx76bh4u40