jsonb_agg with an InstrumentedAttribute

[Julien Cigar]

Hello,

I'd like to use a sql.func.jsonb_agg() with the following
InstrumentedAttribute:

orm.mapper(
Question, tables['amnesia_phc_registry.question'],
properties={
'choices': orm.relationship(
Choice, back_populates='question'
),

'answers_text': orm.relationship(
AnswerText, back_populates='question'
)
}
)


q1 = (
self.dbsession.query(Question)
.join(Question.choices)
.join(Choice.experts)
.filter(Choice.experts.contains(self.entity))
.group_by(Question.id)
).add_columns(
sql.func.jsonb_agg(Question.choices)
)

but I'm getting a list of [True, True], for example:
(<amnesia_phc_registry.model.Question object at 0x805e90850>, [True, True, True, True])

this is because the generated query is

jsonb_agg(amnesia_phc_registry.question.id = amnesia_phc_registry.question_choice.question_id) rather than jsonb_agg(amnesia_phc_registry.question_choice)

any idea how could I achieve this ?

Thanks!
Julien

--
Julien Cigar
Belgian Biodiversity Platform (http://www.biodiversity.be)
PGP fingerprint: EEF9 F697 4B68 D275 7B11 6A25 B2BB 3710 A204 23C0
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

[Mike Bayer]

is "question_choice" a table name?   use json_agg(literal_column("question_choice"))

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/20200625093846.63f6ssijswsvx6gu%40x1.

[Julien Cigar]

On Thu, Jun 25, 2020 at 12:07:40PM -0400, Mike Bayer wrote:
> is "question_choice" a table name? use json_agg(literal_column("question_choice"))

Thank you, it works with literal_column() :)

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/61045d8f-1733-4f51-8b93-4af41d043b7d%40www.fastmail.com.