Mapping array to composite type to a different row type

Question

I want to map an array of key value pairs of GroupCount to a composite type of GroupsResult mapping only specific keys.

I'm using unnest to turn the array into rows, and then use 3 separate select statements to pull out the values.
This feels like a lot of code for something so simple.

Is there an easier / more concise way to do the mapping from the array type to the GroupsResult type?

create type GroupCount AS (
    Name    text,
    Count   int
);

create type GroupsResult AS (
    Cats  int,
    Dogs  int,
    Birds int
);


WITH unnestedTable AS (WITH resultTable AS (SELECT ARRAY [ ('Cats', 5)::GroupCount, ('Dogs', 2)::GroupCount ] resp)
                    SELECT unnest(resp)::GroupCount t
                    FROM resultTable)
SELECT (
        (SELECT (unnestedTable.t::GroupCount).count FROM unnestedTable WHERE (unnestedTable.t::GroupCount).name = 'Cats'),
        (SELECT (unnestedTable.t::GroupCount).count FROM unnestedTable WHERE (unnestedTable.t::GroupCount).name = 'Dogs'),
        (SELECT (unnestedTable.t::GroupCount).count FROM unnestedTable WHERE (unnestedTable.t::GroupCount).name = 'Birds')
)::GroupsResult

fiddle

http://sqlfiddle.com/#!17/56aa2/1

Erwin Brandstetter · Accepted Answer · 2021-12-21T23:15:23.077

A bit simpler. :)

SELECT (min(u.count) FILTER (WHERE name = 'Cats')
      , min(u.count) FILTER (WHERE name = 'Dogs')
      , min(u.count) FILTER (WHERE name = 'Birds'))::GroupsResult
FROM   unnest('{"(Cats,5)","(Dogs,2)"}'::GroupCount[]) u;

db<>fiddle here

See:

Aggregate columns with additional (distinct) filters

Subtle difference: our original raises an exception if one of the names pops up more than once, while this will just return the minimum count. May or may not be what you want - or be irrelevant if duplicates can never occur.

For many different names, crosstab() is typically faster. See:

PostgreSQL Crosstab Query

Mapping array to composite type to a different row type

1 Answers1