Close modal

Blog Post

Unnest with Postgresql

Development
Mon 05 September 2016
0 Comments


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:

devices
{"(925f74791aad51e496cc08794bcc8e51e279f75bf9fe0caf2c78c38ddb288026,1),(57d7cfc2d0d471c1740c13c352aa137ae57cb9ee343a761be3327dec22b3494a,1)"}

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);
DeviceId DeviceType
"57d7cfc2d0d471c1740c13c352aa137ae57cb9ee343a761be3327dec22b3494a" 1
"925f74791aad51e496cc08794bcc8e51e279f75bf9fe0caf2c78c38ddb288026" 1
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. ]


Comments !