List comprehensions with PostgreSQL arrays

Question

I've got a PostgreSQL table called composite_author, with an array of a user type in the posts field.

select * from composite_author

|id |name |posts                                        |
|---|-----|---------------------------------------------|
|1  |john |{"(2,1,\"new post\")","(1,1,\"first post\")"}|
|2  |edgar|{"(3,2,\"hello world\")"}|                   |

The user type in the posts column has column names id, author_id, post_text.

I would like to write a query that produces a result that has the id and name columns, as well as a column with an array of strings representing just the text of each post. The ideal output follows:

|id |name |posts                                        |
|---|-----|---------------------------------------------|
|1  |john |{"new post","first post"}                    |
|2  |edgar|{"hello world"}                              |

It would also be nice to perform other types of manipulations, like returning an array of tuples containing just the post id and post text, or filtering the elements of the array based on some criteria. Essentially, I'd sort of like my selection to behave something like a list comprehension in python, or a simple linq statement in C#.

What is the syntax for queries like these?

Answer 1

Given a user type post as

create type post as (
    id int,
    author_id int,
    post_text text
);

and a composite_author table as

create table composite_author (
    id int,
    name text,
    posts post[]
);
insert into composite_author (id, name, posts) values
(1, 'john', '{"(2,1,\"new post\")","(1,1,\"first post\")"}'),
(2, 'edgar', '{"(3,2,\"hello world\")"}');

Then the basic query is

select id, name, (p).id as post_id, (p).author_id, (p).post_text
from (
    select id, name, unnest(posts) as p
    from composite_author
) s;
 id | name  | post_id | author_id |  post_text  
----+-------+---------+-----------+-------------
  1 | john  |       2 |         1 | new post
  1 | john  |       1 |         1 | first post
  2 | edgar |       3 |         2 | hello world

It can be turned into a view as

create view view_composite_author as
select id, name, (p).id as post_id, (p).author_id, (p).post_text
from (
    select id, name, unnest(posts) as p
    from composite_author
) s;

Then the basic query gets much simpler

select *
from view_composite_author;

The array of strings:

select id, name, array_agg(post_text)
from view_composite_author
group by id, name;
 id | name  |         array_agg         
----+-------+---------------------------
  1 | john  | {"new post","first post"}
  2 | edgar | {"hello world"}

The array of tuples containing post_id and post_text

select array_agg((post_id, post_text))
from view_composite_author;
                            array_agg                            
-----------------------------------------------------------------
 {"(2,\"new post\")","(1,\"first post\")","(3,\"hello world\")"}

Filtering

select array_agg((post_id, post_text))
from view_composite_author
where author_id = 1;
                 array_agg                 
-------------------------------------------
 {"(2,\"new post\")","(1,\"first post\")"}

Answer 2

I would strongly recommend normalizing that data considerably, if at all possible, as everything being mashed together like that can be pretty restrictive.

However, you can try the following given the current data:

create table foo
(
  id integer,
  name text,
  posts text[]
);

insert into foo (id, name, posts)
  values 
      (1, 'john',  '{{"(2,1,\"new post\")"}, {"(1,1,\"first post\")"}}'),
      (2, 'edgar', '{"(3,2,\"hello world\")"}');

with indiv_strs AS
(
select id, name, unnest(posts) as post
from foo
)
select id, name, unnest(regexp_matches(post, E'\"(.+)\"')) as filtered_post
from indiv_strs;

This results in output like this:

1   john    new post
1   john    first post
2   edgar   hello world

sqlfiddle

And at that point, since things are more normalized, you can use regular queries to mix and match as needed. (You could put the results of the above query in a temp table first.)