# ARRAY\_AGG Published articles for ARRAY\_AGG. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## How to Filter a SQL Nested Collection by a Value DevFeed: [How to Filter a SQL Nested Collection by a Value]() Original publisher: [Read original article]() Author: lukaseder Published: 2022-06-10T14:32:38Z Content type: tutorial Language: en Sources: [jOOQ]() Topics: [SQL](), [PostgreSQL](), [Stack Overflow]() Tags: [array](), [array-agg](), [derived-table](), [filter](), [how-to](), [jooq](), [multiset](), [multiset-agg](), [nested-collections](), [order-of-operations](), [postgresql](), [predicate](), [sql]() ### AI overview This tutorial explains how to filter SQL nested collections by a value. It compares filtering arrays through a derived table with using PostgreSQL's ARRAY_AGG and group filtering, then shows a jOOQ version intended to work across supported relational database systems. ### Source excerpt I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ's MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, ... Continue reading How to Filter a SQL Nested Collection by a Value -> ## PostgreSQL Data Types: Arrays DevFeed: [PostgreSQL Data Types: Arrays]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2018-04-20T12:47:25Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [PostgreSQL](), [Databases]() Tags: [array-agg](), [arrays](), [data-type](), [dataset](), [indexing](), [postgresql](), [query](), [sql]() ### AI overview This tutorial introduces PostgreSQL array data types, explaining when arrays can replace lookup tables and how to work with array elements in SQL. It demonstrates importing geolocated tweets, extracting hashtag arrays, aggregating matches, and using GIN indexing to query array contents. ### Source excerpt Continuing our series of PostgreSQL Data Types today we're going to introduce the PostgreSQL array data types. Arrays can be used to denormalize data and avoid lookup tables. A good rule of thumb for using them that way is that you mostly use the array as a whole, even if you might at times search for elements in the array. Heavier processing is going to be more complex than a lookup table. ## The best Postgres feature you're not using - CTEs aka WITH clauses DevFeed: [The best Postgres feature you're not using - CTEs aka WITH clauses]() Original publisher: [Read original article]() Author: Map Published: 2013-11-18T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [SQL](), [Query (disambiguation)](), [Databases]() Tags: [array-agg](), [cte](), [feature](), [join](), [performance](), [postgres](), [postgresql](), [readability](), [sql]() ### AI overview This article explains how PostgreSQL common table expressions (CTEs), also called WITH clauses, make SQL queries more readable and composable. It demonstrates chaining CTEs to analyze tasks assigned to users and identify users responsible for more than half of a project's tasks, while noting that readability may involve a performance tradeoff. ### Source excerpt SQL by default isn't typically friendly to dive into, and especially so if you're reading someone else's already created queries. For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. I was recently reminded of a key feature in Postgres that most don't use by @timonk highlighting it in his AWS Re:Invent Redshift talk. The simple feature actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be. The feature itself is known as CTEs or common table expressions, you may also here it referred to as WITH clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you're doing. Lets take a look at a nice simple one: WITH users_tasks AS ( SELECT users.email, array_agg(tasks.name) as task_list, projects.title FROM users, tasks, project WHERE users.id = tasks.user_id projects.title = tasks.project_id GROUP BY users.email, projects.title ) Using this I could now just append some basic other query on to the end that references this CTE users_tasks. Something akin to: SELECT * FROM users_tasks; But where it becomes more interesting is chaining these together. So while I have all tasks assigned to each user here, perhaps I want to then find which users are responsible for more than 50% of the tasks on a given project, thus being the bottleneck. To oversimplify this we could do it a couple of ways, total up the tasks for each project, and then total up the tasks for each user per project: total_tasks_per_project AS ( SELECT project_id, count(*) as task_count FROM tasks GROUP BY project_id ), tasks_per_project_per_user AS ( SELECT user_id, project_id, count(*) as task_count FROM tasks GROUP BY user_id, ## Understanding Window Functions DevFeed: [Understanding Window Functions]() Original publisher: [Read original article]() Author: Dimitri Fontaine PostgreSQL Major Contributor; Author Published: 2013-08-20T10:04:00Z Content type: tutorial Language: en Sources: [Dimitri Fontaine]() Topics: [SQL](), [PostgreSQL](), [Query (disambiguation)](), [Databases]() Tags: [aggregate](), [array-agg](), [article](), [database](), [functions](), [partitioning](), [postgresql](), [query](), [sql](), [window-functions]() ### AI overview A tutorial explaining SQL window functions, including window frames, aggregate calculations, partitioning, and practical examples using PostgreSQL and motor racing data. ### Source excerpt There was SQL before window functions and SQL after window functions: that's how powerful this tool is. Being that of a deal breaker unfortunately means that it can be quite hard to grasp the feature. This article aims at making it crystal clear so that you can begin using it today and are able to reason about it and recognize cases where you want to be using window functions. *We see a part of the data as if through a little window* ## Using array\_agg in Postgres - powerful and flexible DevFeed: [Using array\_agg in Postgres - powerful and flexible]() Original publisher: [Read original article]() Author: Map Published: 2013-04-17T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Query (disambiguation)](), [function]() Tags: [aggregate](), [array](), [array-agg](), [email](), [functions](), [postgres](), [query]() ### AI overview This tutorial explains how to use PostgreSQL arrays and the array_agg function to aggregate project and task data into formatted results, reducing the need to group query results in application code. It demonstrates the approach with a project-management email example. ### Source excerpt In almost any application it's common to want to aggregate some set of values together, commonly in a comma separated form. Most developers do this by running a query to get much of the raw data, looping over the data and pushing it into a set, appending each new value to the appropriate key. Hopefully, it's not a surprise that there's a much better way to do this with PostgreSQL. Postgres has a flexible and robust array datatype that comes with a variety of functions. Even without taking advantage of the array datatype in your application, you can still take advantage of some of the functions to get the functionality you need. Lets take a look at an example schema and use case. An example Given a project management application, you may have users who have projects that have tasks. An example piece of functionality might be to send an email with a list of all projects that have tasks that are past their due dates of completion. Your schema might look something like this: # \d users Table "public.users" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | not null email | character varying(255) | ... # \d projects Table "public.projects" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | not null user_id | integer | not null name | character varying(255) | not null ... # \d tasks Table "public.tasks" Column | Type | Modifiers --------------+-----------------------------+----------- id | integer | not null project_id | integer | not null completed_at | timestamp without time zone | due_at | timestamp without time zone | ... To get a list of all projects that have tasks that haven't been completed, you would start with something like: SELECT projects.name FROM projects, tasks WHERE projects.id = tasks.project_id AND tasks.due_at > tasks.completed_at AND tasks.due_at > now() This would give you a list of projects which you could then easily join this with users: SELECT users.email pr ## Reasons to Use Postgres DevFeed: [Reasons to Use Postgres]() Original publisher: [Read original article]() Author: Map Published: 2012-04-30T20:55:56Z Content type: opinion Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [Replication](), [functions](), [SQL]() Tags: [array-agg](), [database](), [development](), [engineering](), [mysql](), [postgres](), [replication](), [sql](), [window-functions]() ### AI overview This article presents reasons to use Postgres, including improved replication, window functions, flexible data types such as arrays, built-in functions, custom languages, and extensions. It also contrasts some capabilities with MySQL, Oracle, and SQL Server. ### Source excerpt This post is a list of many of the reasons to use Postgres, much this content as well as how to use these features will later be curated within PostgresGuide.com. If you need to get started check out Postgres.app for Mac, or get a Cloud instance at Heroku Postgres for free UPDATE: A part 2 has been posted on Why Use Postgres Very often recently I find myself explaining why Postgres is so great. In an effort to save myself a bit of time in repeating this, I though it best to consolidate why Postgres is so great and dispel some of the historical arguments against it. Replication For some time the biggest argument for MySQL over Postgres was the lack of a good replication story for Postgres. With the release of 8.4 Postgres's story around replication quickly became much better. While replication is indeed very important, are users actually setting up replication each time with MySQL or is it to only have the option later? Window functions This is a feature those familiar with Oracle greatly missed in Postgres. In fact even SQL Server had some form of them, though it was with T-SQL, which adds a bit more complexity to the feature. This is a feature that once you have you can't live without; the other options that existed before were slower and much more complicated. With the release of 8.4 window functions were added to bring Postgres on par with Oracle in this area. For more info on using them check the Postgres docs above or PostgresGuide.com. Flexible Datatypes Creating a custom column is simpler in Postgres than any other database I've used by far. Excluding custom datatypes, even Postgres's out of the box datatypes make Postgres stand out far ahead of other databases. In particular the ability to create a column as an Array of some datatype. Want to store a game of tic-tac-toe in a database, or an array of 1 user's phone numbers? It simply becomes a single column that can contain multiple phone numbers for a user. Functions Need to do some logic outside of standard