How to insert an element into a nested JSONB array with SQLAlchemy on postgres

Question

I've seen a wide variety of good answers on how to store JSONB in postgres, but as of Postgres 9.5, we are now able to insert into the existing JSONB array without updating the data in the column. None of the material I can find has this documented anywhere, and since I'm new at SQLAlchemy (and python somewhat), reading the code isn't really helping me as much as I'd like.

I'm using Postgres 10.9, python 3.7 and SQLAlchemy 1.3.8 (with the GINO wrapper)

This is just the latest attempt, but there have been many with a myriad of different errors:

await gathering. \
        update(participation=func.jsonb_insert("participation",
                                                "{applications}",
                                                func.to_jsonb(json.dumps(application)))). \
        apply()

In the participation column I have a JSONB object

{ 
    "applications": [ { ... element to be appended} ] 
}

In this particular case, the code yields an error:

could not determine polymorphic type because input has type unknown

score 2 · Answer 1 · answered Sep 08 '19 at 21:30

Figured it out..

ajs = json.dumps(application)
await gathering. \
                update(participation=func.jsonb_insert(
                       json.dumps(gathering.participation), 
                       ["applications","0"],  ajs)). \
                apply()

Here's to trial and error. There has GOT to be a more elegant solution than this.

How to insert an element into a nested JSONB array with SQLAlchemy on postgres

1 Answers1