CODEWITHSUNDEEP
sqlarrayspostgresqljoin
Jake Alsemgeest
Jake Alsemgeest

Reputation: 702

Postgres join with array type resulting in array column

I've seen similar questions posted on here, but none of the solutions I've found have seemed to give me the results I'm looking for.

So I have two tables:

Cards table has collectible cards with unique IDs and additional metadata (casting cost, etc.):

Cards
| id INTEGER | name TEXT | ... |

Prints table contains printing information for cards from Cards table (this is because the same card can be printed in different versions through different card sets):

Prints
| id INTEGER | card_id INTEGER | set TEXT | ... |

What I would like to do is to be able to query for a given card, but include all printings for said card within a single row response. I've been able to do that, somewhat, but instead if there are 6 printings for a card it will result in 6 rows, each row containing all the printing information as a string in JSON format.

The query I've been trying to use is the following:

SELECT DISTINCT(cards.id), cards.*, array_agg(printings.*) FROM cards
LEFT JOIN printings ON cards.id=printings.card_id
WHERE cards.name="C1" 
GROUP BY cards.id,printings.id ORDER BY cards.id;

This has resulted in output like (and the printing_information was just a string):

| id | name | ... |        printing_information            |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |

What I would like to be able to do is join the prints with the cards such that the response would produce:

| id | name | ... |        printing_information            |
|  1 | C1   | ... | { print1Info, print2Info, print3Info } |
|  2 | C2   | ... | { print1Info, print2Info }             |

Is it worth doing all of this in a single query? I could achieve this with some minor post processing, but would that be as performant as it would require multiple queries and would not be that pretty since it could be for multiple cards and search for multiple ids seems messy.

Any help would be appreciated!

Upvotes: 1

Views: 482

Answers (1)

user330315
user330315

Reputation:

You need to aggregate the prints table first, then join to the cards table.

SELECT c.*, p.printing_information
FROM cards c
  LEFT JOIN (
    select pr.card_id, jsonb_agg(to_jsonb(pr))  as printing_information
    from printings pr
    group by pr.card_id 
  ) p ON c.id = p.card_id
WHERE c.name = 'C1'
ORDER BY c.id;

to_jsonb(pr) converts the whole row from the printings table into a JSON value. This is then aggregated into a JSON array using jsonb_agg() for all printings of a card. If you want to remove some columns from the JSON, e.g. the card_id and 'id' columns from the JSON, you can use to_jsonb(pr) - 'card_id' - 'id'

Upvotes: 2

Related Questions