It’s like the arrays have been pivoted (or unpivoted, depending on your point of view). Say you have an Athena table called cities_and_countries that is set up to read JSON data looking like this: UNNEST is a bit peculiar as it is is an operator that produces a relation, unlike most functions which transform or aggregate scalar values. In this article I will cover how to flatten arrays to rows, how to flatten maps to rows, but also when you should be using UNNEST. In data formats like JSON it’s very common to have arrays and map properties, and one question that often comes up is how you flatten these structures to work better in a traditional tabular format – in other words, how to turn array elements into rows. Most of the time you also want something flat as output, as Athena’s CSV output format isn’t really suitable for complex values, and most consumers of the output probably don’t handle complex values either. This makes it possible to do pretty advanced things, but it’s not always easy to wrap your head around what’s going on since almost everything in the SQL world was made for scalar column values. Queries can also aggregate rows into arrays and maps. In fact, they can be deep structures of arrays and maps nested within each other. In contrast to many relational databases, Athena’s columns don’t have to be scalar values like strings and numbers, they can also be arrays and maps.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |