PostgreSQL - Return result from query and distinct possible values

Question

Say I have a table like this:

CREATE TABLE item (
    id text NOT NULL,
    price integer NOT NULL,
    colors text[] DEFAULT ARRAY[]::text[],
    sizes text[] DEFAULT ARRAY[]::text[]
);

And data like this:

INSERT INTO item VALUES ('1', 100, array['red', 'blue', 'green'], array['s', 'l']);
INSERT INTO item VALUES ('2', 5000, array['yellow', 'green'], array['s']);
INSERT INTO item VALUES ('3', 300, array['red'], array['s', 'l', 'xl']);
INSERT INTO item VALUES ('4', 150, array['white'], array['xxl']);

Now suppose I have this query:

SELECT * from item WHERE price > 50 AND price < 400 AND colors&&ARRAY['red'] AND sizes&&ARRAY['s', 'l', 'xxl'] ORDER BY price OFFSET 0 LIMIT 10;

The result set is

 | id | price | colors             | sizes
1| 1  | 100   | {red, blue, green} | {s, l}
1| 3  | 300   | {red}              | {s, l, xl}

My question is: How can I change the query to return not only this result set, but also all possible sizes of this query (excluding any size condition, i.e. excluding sizes&&ARRAY['s', 'l', 'xxl']), all possible colors of this query (excluding only color conditions, i.e. excluding colors&&ARRAY['red']) and the price range ignoring only the price conditions in the query (i.e. excluding price > 50 AND price < 400)? Something like:

 | id | price | colors             | sizes      | p_sizes    | p_colors                          | min_price | max_price |
1| 1  | 100   | {red, blue, green} | {s, l}     | {s, l, xl} | {'red', 'blue', 'green', 'white'} | 100       | 300       |
1| 3  | 300   | {red}              | {s, l, xl} | {s, l, xl} | {'red', 'blue', 'green', 'white'} | 100       | 300       |

Where p_colors is the list of distinct colors for the query:

SELECT * from item WHERE price > 50 AND price < 400 AND sizes&&ARRAY['s', 'l', 'xxl'] ORDER BY price OFFSET 0 LIMIT 10;

 | id | price | colors             | sizes
1| 1  | 100   | {red, blue, green} | {s, l}
1| 4  | 150   | {white}            | {xxl}
1| 3  | 300   | {red}              | {s, l, xl}

p_sizes is the list of distinct sizes for the query:

SELECT * from item WHERE price > 50 AND price < 400 AND colors&&ARRAY['red'] ORDER BY price OFFSET 0 LIMIT 10;

 | id | price | colors             | sizes
1| 1  | 100   | {red, blue, green} | {s, l}
1| 3  | 300   | {red}              | {s, l, xl}

And min_price and max_price are the price range for the query:

SELECT * from item WHERE colors&&ARRAY['red'] AND sizes&&ARRAY['s', 'l', 'xxl'] ORDER BY price OFFSET 0 LIMIT 10;
 | id | price | colors             | sizes
1| 1  | 100   | {red, blue, green} | {s, l}
1| 3  | 300   | {red}              | {s, l, xl}

----- Edit -----

Added a size condition and changed the price of the item with id 2 to 5000 to clarify the problem.

klin · Accepted Answer · 2015-10-22 02:27:42Z

2

This query selects expected values:

select 
    array(select distinct unnest(colors) from item) all_colors,
    array(select distinct unnest(sizes) from item) all_sizes,
    min(price) min_price,
    max(price) max_price
from item;

          all_colors           |  all_sizes   | min_price | max_price 
-------------------------------+--------------+-----------+-----------
 {yellow,blue,white,green,red} | {xl,s,l,xxl} |       100 |       300
(1 row)

You can join the result of the query with your original query in lateral join:

select * 
from item,
lateral (
    select 
        array(select distinct unnest(colors) from item) all_colors,
        array(select distinct unnest(sizes) from item) all_sizes,
        min(price) min_price,
        max(price) max_price
    from item
) sub
where price > 50 and price < 400 AND colors&&ARRAY['red'] order by price;

 id | price |      colors      |  sizes   |          all_colors           |  all_sizes   | min_price | max_price 
----+-------+------------------+----------+-------------------------------+--------------+-----------+-----------
 1  |   100 | {red,blue,green} | {s,l}    | {yellow,blue,white,green,red} | {xl,s,l,xxl} |       100 |       300
 3  |   300 | {red}            | {s,l,xl} | {yellow,blue,white,green,red} | {xl,s,l,xxl} |       100 |       300
(2 rows)

You can split the aggregate query to three parts to have different where clauses, e.g.:

select * 
from item,
lateral (
    select array(
        select distinct unnest(colors) 
        from item 
        where price > 50 and price < 400) all_colors) q1,
lateral (
    select array(
        select distinct unnest(sizes) 
        from item
        where price > 50 and price < 400 and colors && ARRAY['red']) all_sizes) q2,
lateral (
    select
        min(price) min_price,
        max(price) max_price
    from item
    where colors&&ARRAY['red']
) q3
where price > 50 and price < 400 and colors&&ARRAY['red'] order by price;

 id | price |      colors      |  sizes   |          all_colors           | all_sizes | min_price | max_price 
----+-------+------------------+----------+-------------------------------+-----------+-----------+-----------
 1  |   100 | {red,blue,green} | {s,l}    | {yellow,white,green,red,blue} | {l,s,xl}  |       100 |       300
 3  |   300 | {red}            | {s,l,xl} | {yellow,white,green,red,blue} | {l,s,xl}  |       100 |       300
(2 rows)

edited Oct 22, 2015 at 2:27

answered Oct 22, 2015 at 1:39

klin

123k15 gold badges240 silver badges262 bronze badges

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

3 Comments

Douglas Henrique Over a year ago

This works for the case where one wants all possible values in the table, but I need to restrain the possibilities to the result of the original query modified to ignore only the column I need the possibilities from. Suppose the item with id 2 has the price 1000. Since it is not red, it is not selected, and max_price should be 300, not 1000. Does this mean that I need to make a new query for every field?

klin Over a year ago

I do not understand. If you want to query only ids 1 and 3 then all_colors should be {green,red,blue} but in the question there is {'red', 'blue', 'green', 'yellow', 'white'}?

Douglas Henrique Over a year ago

Because I don't want to query only ids 1 and 3. To get all_colors, I need to make the same query, but without the colors&&ARRAY['red'] condition. That leaves us with only price > 50 AND price < 400. For this case, it just happens that every item has the price in this range. Maybe it was a bad example. I'll edit the question with more data.

Collectives™ on Stack Overflow

PostgreSQL - Return result from query and distinct possible values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related