Combined SELECT from unnested composite type array and regular column

Question

I have a table my_friends_cards:

id | name    | rare_cards_composite[] |
---+---------+------------------------
1  | 'timmy' |  { {1923, 'baberuth'}, {1999, 'jeter'}}
2  |'jimmy'  |  { {1955, 'Joey D'}, {1995, 'juice_head'}}
3  |'bob'    |  {{2001, 'mo_jeter'}}

I want to make the a request kinda like this:

Select name, (cards.x).player
FROM SELECT UNNEST(base_ball_card) as x
     FROM my_friends_cards
     WHERE name=ANY(['timmy', 'jimmy'])) as cards
WHERE (cards.x).year > 1990

(I know this doesn't work that there is no 'name' field in the unnested composite array.)

I am getting the feeling that my composite type array column should just be another table, and then I could do a join, but is there anyway around this?

I would expect this result:

[('timmy', 'jeter')
,('jimmy', 'juice_head')]

version: PostgreSQL 9.3.3

Answer 1

Your feeling is correct: a normalized schema with another table instead of the array of composite types would be the superior approach in many respects.

While stuck with your unfortunate design:

Test setup

(You should have provided this.)

CREATE TYPE card AS (year int, cardname text);

CREATE TABLE my_friends_cards (id int, name text, rare_cards_composite card[]);
    
INSERT INTO my_friends_cards VALUES
  (1, 'timmy', '{"(1923,baberuth)","(1999,jeter)"}')
, (2, 'jimmy', '{"(1955,Joey D)","(1995,juice_head)"}')
, (3, 'bob'  , '{"(2001,mo_jeter)"}')
;

Query

Requires Postgres 9.3+.

SELECT t.name, c.cardname
FROM   my_friends_cards t
     , unnest(t.rare_cards_composite) c
WHERE t.name = ANY('{timmy,jimmy}')
AND   c.year > 1990;

db<>fiddle here
_{Old sqlfiddle}

Note that the composite type is decomposed in the unnesting.