INET and CDIR types are useful in that they can be encoded into approx 7-19 bytes based on IPv4 or IPv6. When an application stores lots of internet addresses this is useful.
JSON type exists in all NuoDB competitors.
Array types (Postgres) have been very useful for storing relatively small lists of scalar values "inline" with the table avoiding a join/separate table. I think every application I have worked on in the past 3 years used and benefited from [Postgres] array types of scalar values (so no multi-column arrays actually).
That is, primarily: varchar, uuid and integer