PostgreSQL – How to Accumulate Values into an Array

aggregateaggregate-filterarraypostgresql

Currently I have this query:

select 
    sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
    sum(case when my_table.property_type_mapped = 'SEMIDETACHED' then my_table.price else 0 end) as Semidetached_Current_Asking_Price
from 
    my_table;

So if my_table has the values:

property_type | price
--------------+-------
FLAT          | 5000
SEMIDETACHED  | 9000
FLAT          | 6000

the query will return:

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000                     | 9000

How can I replace the sum to accumulate the values into arrays to get?

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000}              | {9000}

Best Answer

If your PostggreSQL version is 9.4 or later use FILTER clause:

select
    array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price,
    array_agg(my_table.price) filter(where my_table.property_type = 'SEMIDETACHED') as Semidetached_Current_Asking_Price
from 
    my_table;

Related Solutions

Postgresql – SELECT INTO with regexp_replace() doesn’t write changes into newly generated table

Your regexp_replace() statement is invalid. It would work like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
FROM (
 VALUES
  ('6''''')
 ,('6''''')
 ,('6''''')
 ,('20''''')
 ,('12''''')
 ,('18''''')
 ,('20''''')
 ,('8''''')
 ,('10''''')
 ,('''''')
 ,('''''')
) tbl(tbl)

Your SELECT INTO statement is invalid. It would look like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
INTO   temp table_4
FROM   temp_2
ORDER  BY id;

But I would use neither.

SELECT INTO is discouraged. Only supported for historical reasons. Use CREATE TABLE AS instead, which is the SQL standard way. Per documentation:

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

Bold emphasis mine.

For the presented examples, rtrim() is much simpler and faster:

CREATE TEMP TABLE table_4 AS 
SELECT rtrim(tbl, '''')     -- trim all trailing '
FROM   temp_2
ORDER  BY id;

Postgresql – Postgres: querying jsonb array with an array

You can use the @> operator.

From the docs:

@> Does the left JSON value contain within it the right value?

So in your case it would look something like this:

select * from products where specs @> '{"spec_options": ["a", "b", "c"]}';

Best Answer

Related Solutions

Postgresql – SELECT INTO with regexp_replace() doesn’t write changes into newly generated table

Postgresql – Postgres: querying jsonb array with an array

Related Question