My postgresql database. I need to get rid of JSON nesting via sqlalchemy.

Data in the groups column groups

How do I create a query in SQLAlchemy?

Expected result

[{"id": 5, "name": "imba"}, {"id": 1, "name": "group1"}, {"id": 3, "name": "group3"}]

CodePudding user response：

Given a table in Postgresql like this

test# table t73051318;
 id_ │                           groups                           
═════╪════════════════════════════════════════════════════════════
   1 │ [{"id": 5, "name": "imba"}]
   2 │ [{"id": 1, "name": "group1"}, {"id": 3, "name": "group3"}]
(2 rows)

we can merge the JSONB values by calling the json_agg function on the result of calling jsonb_array_elements on the groups column.

test# select json_agg(row) as aggregated
        from (select jsonb_array_elements(groups) as row
                from t73051318
        ) q;
                                      aggregated                                       
═══════════════════════════════════════════════════════════════════════════════════════
 [{"id": 5, "name": "imba"}, {"id": 1, "name": "group1"}, {"id": 3, "name": "group3"}]
(1 row)

In SQLAlchemy core, the code would look like this:

import sqlalchemy as sa

# Create a subquery for the jsonb_array_elements call.
subq = sa.select(
    sa.func.jsonb_array_elements(tbl.c.groups).label('row')
).subquery()

# Aggregate the subquery result.
stmt = sa.select(sa.func.json_agg(subq.c.row).label('aggregated'))

with engine.connect() as conn:
    res = conn.execute(stmt)
    print(next(res).aggregated)

In Flask-SQLAlchemy, the code would be

# Create a subquery for the jsonb_array_elements call.
subq = db.session.query(
    db.func.jsonb_array_elements(MyModel.groups).label('row')
).subquery()

# Aggregate the subquery result.
stmt = db.session.query(db.func.json_agg(subq.c.row).label('aggregated'))

result = stmt.first()