Unnest with Postgresql

It's great being able to have array type columns in postgresql, especially composite type arrays which are effectively a table inside a column sometimes. Sometimes you might only the nested composite type as you've already used some search condition on the owning row to filter it in. If we take the data as-is, with SQL queries (will appear as a blob of string record) or even ORM mappers (at best will split it into anonymous type or similar), it becomes more onerous to deal with.

How this looks normally

SELECT devices
FROM subscribers
WHERE subscribers.filter_categories & 4096 > 0) as a ;

Running this query will produce output like the below:

As you can see it will be annoying to work with if we wish to break out the rows and especially indivudual items. Let's look at a useful postgresql command that will help us.

How this looks when we use unnest

SELECT (unnest(val)).*
FROM (
    SELECT devices
    FROM subscribers
    WHERE subscribers.filter_categories & 4096 > 0
    ) c(val);

Conclusion

As you can see, although small it is a very useful tool that lets us look at the data entirely differently, as if they were a different table all to themselves, but with the convenience of being embedded types. ]