python - PostgreSQL HStore() field

Question

I use multiple PostgreSQL HStore fields in my django model. Keys of HStore fields are big int numbers that are related to some other fields in application and are therefore random, not predefined. Values are decimal numbers. Like so: "10intNumber" => "DecNumber". All hstore fields have the same 10IntNumber keys in them, even if DecNumber is 0. The model also include a foreign key to user, and a status.

• Status is 1: only the hstore_field_1 is evaluated and summed.
• Status is 0: both hstore_field_1 and hstore_field_2 are evaluated and summed.

I use a SQL Query like this, to sum all the DecNumber in values for predefined 10intNumber, where d.number is the 10IntNumber from before and is retrieved from another queryset.:

query = f"""
    SELECT
        SUM(hstore_1::numeric) AS hstore_1_field,
        SUM(hstore_2::numeric) as hstore_2_field,
    FROM (
        SELECT
            UNNEST(hstore_field_1-> ARRAY {[str(d.number) for d in queryset]}) AS hstore_1,
            UNNEST(CASE WHEN status = '0' THEN hstore_field_2-> ARRAY {[str(d.number) for d in queryset]} ELSE ARRAY ['0'] END) AS hstore_2,
            FROM
                public.app_model
            WHERE
                hstore_field_1?| ARRAY {[str(d.number) for d in queryset]}
          ) AS b;
    """

I also use a SQL query to group sums by related user_id and user__othemodel_id. But my knowledge of SQL comes short afterwards.

I would like to sum everything as mentioned before, but would like to group summaries by 10intNumber. So that the result would look like:

10intNumber	Sum Hstore 1	Sum Hstore 2
xxxxxxxxxxx	Decimal(y.yy)	Decimal(y.yy)
xxxxxxxxxxx	Decimal(y.yy)	Decimal(y.yy)

question from:https://stackoverflow.com/questions/65841408/postgresql-hstore-field

Answer 1

Waitting for answers