Query against JSON array in PostgreSQL
You can write the query simpler without CTE which probably would perform better.

select (jsonb_array_elements(transaction-&gt;'transactions')-&gt;'id')::text as trx_id 
from transactions;


SQL alchemy returns a list of tuples. This is the standard behaviour. A row in PostgreSQL is represented as a tuple. Even if you query a single columns it's going to be a tuple with one element. Hence the (u'ABC123',) representation.

If you need a list of the first column you can easily transform your resultset in python:

[r[0] for r in rowset.fetchall()]


This should return 

['ABC123', 'DEF456', 'GHI789']