CODEWITHSUNDEEP
javascriptarraysknex.js
Pratap Sharma
Pratap Sharma

Reputation: 2753

How to select the first element of a column of type array column using knex.select()?

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.

Upvotes: 0

Views: 12973

Answers (2)

Mikael Lepistö
Mikael Lepistö

Reputation: 19728

https://www.postgresql.org/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.com/embed/1apx76bh4u40

Upvotes: 0

user45392
user45392

Reputation: 622

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]);
})

Upvotes: 3

Related Questions