Search code examples
node.jspostgresqlknex.js

How to create a column that can store an array of objects using knex/postgres

I'm looking to store a simple array on a table. The array should hold objects.

I can't find any examples of how to create this column online... I see maybe jsonb?

I don't see an array option in the docs though...

http://knexjs.org/#Schema-jsonb

I'm using knex + postgres + node.js

Solution

  • Knex has 2 methods to store jsons in a relational db.

    json for storing json in a human readable way.

    jsonb, binary way of store.

    For PostgreSQL, due to incompatibility between native array and json types, when setting an array (or a value that could be an array) as the value of a json or jsonb column, you should use JSON.stringify() to convert your value to a string prior to passing it to the query builder, e.g.

    knex.table('users')
  .where({id: 1})
  .update({json_data: JSON.stringify(mightBeAnArray)});