Insert/update JSON into Postgresql column WHERE myvar = myval

Question

I'm trying to insert JSON into a Postgresql column who's data type is JSON, but I'm having trouble finding how I can do this. This is as far as I've gotten but it's not correct because it just overwrites it every time, instead of adding a new key pair.

I'm using pg-promise node module to perform these queries. Here's what I have so far:

db.query("UPDATE meditation_database SET completed=$1 WHERE user_id=$2", [{myVar : true}, user_id]);

Also 'myVar' should be updated to the variable value, but instead it treats it as a string. How can I get the actual value of 'myVar' instead of it being treated literally.

Thanks,

For the last part, using variable `$1^` instead of `$1` will treat it as is, without formatting (see [raw-text variables](https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#raw-text)). But PostgreSQL expects a string there. Updating JSON though is another question. — vitaly-t, Nov 21 '15 at 02:55

score 0 · Answer 1 · edited May 23 '17 at 12:15

I'm trying to insert JSON into a Postgresql column who's data type is JSON, but I'm having trouble finding how I can do this.

By executing this:

db.query("INSERT INTO meditation_database(completed, user_id) VALUES($1, $2)", 
          [{myVar : true}, user_id]);

Also 'myVar' should be updated to the variable value, but instead it treats it as a string. How can I get the actual value of 'myVar' instead of it being treated literally.

myVar is serialized into JSON as a string, that's the proper JSON format for property names, and is the only format that PostgreSQL will accept.

This is as far as I've gotten but it's not correct because it just overwrites it every time, instead of adding a new key pair.

If you are asking how to update JSON in PostgreSQL, this question has been answered previously, and in great detail: How do I modify fields inside the new PostgreSQL JSON datatype?

Insert/update JSON into Postgresql column WHERE myvar = myval

1 Answers1