Postgresql array contains quantity of entries?

Question

So I know that with a postgresql array I can do a query like:

SELECT * 
FROM deck 
WHERE card_ids_array @> ARRAY[
    'd438faa9-7920-437a-8d1c-682fade5d350',
    'b361d7e2-6873-4890-8f87-702d9c89c5ad'
];

to get all the decks with those values in the array. But my array might have multiple copies of the same card id. How can I do a query like:

SELECT * 
FROM deck 
WHERE card_ids_array @> ARRAY[
    'd438faa9-7920-437a-8d1c-682fade5d350',
    'b361d7e2-6873-4890-8f87-702d9c89c5ad',
    'b361d7e2-6873-4890-8f87-702d9c89c5ad'
];

and get only decks with two instances of 'b361d7e2-6873-4890-8f87-702d9c89c5ad' in their card_ids_array? When I do that query, I get all decks with at least one of the card ids, not the ones with at least one of the first id and at least two of the second.

My only idea so far is to change my array to contain the quantity of cards in it as well, and add another entry for each quantity. Like 'b361d7e2-6873-4890-8f87-702d9c89c5ad-1', 'b361d7e2-6873-4890-8f87-702d9c89c5ad-2', and then I could do a query for 1 through 6 if the query is for just one instance, or just 6 if they only want decks with 6 or more instances of the card.

GMB · Accepted Answer · 2019-02-09 20:48:47Z

1

One solution would be to use array_positions() to get the list of occurences of the searched string within the array, and then array_length() to verify the result count. To check for the presence of the first string, one could also use array_position():

SELECT * 
FROM deck 
WHERE 
    array_position(card_ids_array, 'd438faa9-7920-437a-8d1c-682fade5d350') > -1
    AND array_length(
        array_positions(card_ids_array, 'b361d7e2-6873-4890-8f87-702d9c89c5ad')
    ) > 1

See the Postgres Array Functions documentation.

edited Feb 9, 2019 at 20:48

answered Feb 9, 2019 at 20:43

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himanshu · Accepted Answer · 2019-02-09 20:35:20Z

0

Either use distinct or use group by to remove dupicates

   SELECT Distinct *  FROM deck WHERE card_ids_array 
     @> ARRAY['d438faa9-7920-437a-8d1c-682fade5d350', 
      'b361d7e2-6873-4890-8f87-702d9c89c5ad'];

or

    Select .... group by card_ids_array having count(*);

answered Feb 9, 2019 at 20:35

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

1 Comment

Nelaina Over a year ago

Hmm, maybe I didn't explain what I'm going for very well. I actually want all the decks with a certain number of card ids in their array. Like if the deck has 3 copies of a card, I want all the decks with 3 or more copies of that card. I'm not having an issue with duplicate deck results.

Collectives™ on Stack Overflow

Postgresql array contains quantity of entries?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related