I am using Postgres DB with SQLAlchemy as toolkit.

My model looks like

class Test(db.Model):

    """Test entry in DB."""
    __tablename__ = 'test'

    key1 = db.Column(db.String, primary_key=True)
    key2 = db.Column(postgresql.ARRAY(JSON))

I want to know how can I add elements to my key2 column which is an array?

For making changes to the DB, let's say I am using something of this kind:

test = Test(key1="key2", key2=array)

db.session.add(test)

How can I add new elements to this array or make a new array and assign it to key2? Basically struggling with proper documentation for JSON Arrays in Postgres/SQLAlchemy.

Any help would be highly appreciated.

share
    
Since JSON itself supports lists, do you have a specific reason for wrapping it in an ARRAY (for example having to work against existing schema)? – Ilja Everilä Oct 12 '16 at 6:46
    
@IljaEverilä :- I want to store a list of JSON objects in key2. The issue I am facing is of casting. array = [] array.append(somedata) test = Test(sha=actual_commit_sha, data=array) It complains ProgrammingError: (psycopg2.ProgrammingError) column "data" is of type json[] but expression is of type text[] LINE 1: ...NTO test (key1, key2) VALUES ('1223!4&&5678!^^^^', ARRAY['{"i... ^ HINT: You will need to rewrite or cast the expression. Any suggestions ? – Akchay Srivastava Oct 12 '16 at 19:19
1  
I was just trying to point out that you could also store lists of objects in a Column(JSON) column as well, but JSON lists do not support slicing, if that is required. With your current model you can use an explicit CAST-expression: test = Test(key1="key2", key2=sqlalchemy.cast(array, postgresql.ARRAY(JSON))), but that is a bit ugly. – Ilja Everilä Oct 13 '16 at 5:10
    
@IljaEverilä :- Thanks. I am using Column(JSON) to store list of objects. But encountering an issue. My JSON doc is basically a list like this [{u'initiator': u'akchay', u'title': u'Test1'}, {u'initiator': u'akchay', u'title': u'Test2}]. Now when I want to update this list, and add another dictionary to it, then the update does not seem to happen. event_row = Test.query.filter_by(key1="key1").first() event_list = event_row.key2 //key2 is JSON doc event_list.append(third_dictionary) event_row.key2 = event_list db.session.commit() There is no update. Can you let me know what's wrong? – Akchay Srivastava Oct 25 '16 at 2:24
    
A super late reply, but: you (probably) lack mutation tracking for starters. Also when you assign event_list back to event_row.key2, you're just assigning the same list back, which in the eyes of SQLA isn't a change at all. – Ilja Everilä Jan 5 at 22:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.