CODEWITHSUNDEEP
postgresqlknex.js
lmngn23
lmngn23

Reputation: 521

How to push data into a "JSON" data type column in Postgresql

I have the following POSTGRESQL table 

 id | name |    email     | weightsovertime |         joined          
 20 | Le   | [email protected] | []              | 2018-06-09 03:17:56.718

I would like to know how to push data (JSON object or just object) into the weightsovertime array.

And since I am making a back-end server, I would like to know the KnexJS query that does this.

I tried the following syntax but it does not work

update tableName set weightsovertime = array_append(weightsovertime,{"weight":55,"date":"2/3/96"}) where id = 20;

Thank you

Upvotes: 0

Views: 2129

Answers (2)

Caio Ferreira
Caio Ferreira

Reputation: 304

For anyone who happens to land on this question, the solution using Knex.js is:

knex('table')
      .where('id', id)
      .update({
        arrayColumn: knex.raw(`arrayColumn || ?::jsonb`, JSON.stringify(arrayToAppend))
})

This will produce a query like:

update tableName 
  set weightsovertime = arrayColumn || $1::json
where id = 20;

Where $1 will be replaced by the value of JSON.stringfy(arrayToAppend). Note that this conversion is obligatory because of a limitation of the Postegre drive

Upvotes: 1

user330315
user330315

Reputation:

array_append is for native arrays - a JSON array inside a jsonb column is something different.

Assuming your weightsovertime is a jsonb (or json) you have to use the concatenation operator : ||

e.g: 

update the_table
  set weitghtsovertime = weightsovertime||'[{"weight": 55, "date": "1996-03-02"}]'
where id = 20;

Online example: http://rextester.com/XBA24609

Upvotes: 0

Related Questions