I have something like the following data structure (greatly simplified and presented here in Spark format since that's what's used in Glue internally):
root
|-- invoiceId: string (nullable = false)
|-- items: array (nullable = false)
| |-- element: struct (containsNull = false)
| | |-- itemId: string (nullable = false)
| | |-- price: long (nullable = false)
| | |-- productId: string (nullable = false)
| | |-- quantity: long (nullable = false)
|-- productGroups: array (nullable = false)
| |-- element: struct (containsNull = false)
| | |-- name: string (nullable = false)
| | |-- number: long (nullable = false)
| | |-- productGroupId: string (nullable = false)
|-- products: array (nullable = false)
| |-- element: struct (containsNull = false)
| | |-- name: string (nullable = false)
| | |-- number: long (nullable = false)
| | |-- productGroupId: string (nullable = false)
| | |-- productId: string (nullable = false)
An example record would look something like:
+---------+--------------------------------------------------------+-------------------+------------------------------------------------------+
|invoiceId|items |productGroups |products |
+---------+--------------------------------------------------------+-------------------+------------------------------------------------------+
|xyz |[{item1Id, 1.0, prod1Id, 1}, {item2Id, 0.5, prod2Id, 3}]|[{X, 100, pgroup1}]|[{A, 10, pgroup1, prod1Id}, {B, 20, pgroup1, prod2Id}]|
+---------+--------------------------------------------------------+-------------------+------------------------------------------------------+
The data is sourced from an application which records e.g. an invoice.
What I would like to do is get this data in a denormalized long-form, with the following target schema:
root
|-- invoiceId: string (nullable = false)
|-- itemId: string (nullable = false)
|-- price: double (nullable = false)
|-- productGroupId: string (nullable = false)
|-- productGroup_name: string (nullable = false)
|-- productGroup_number: long (nullable = false)
|-- productId: string (nullable = false)
|-- product_name: string (nullable = false)
|-- product_number: long (nullable = false)
|-- quantity: long (nullable = false)
which would look like the following:
+---------+-------+-----+--------------+-----------------+-------------------+---------+------------+--------------+--------+
|invoiceId|itemId |price|productGroupId|productGroup_name|productGroup_number|productId|product_name|product_number|quantity|
+---------+-------+-----+--------------+-----------------+-------------------+---------+------------+--------------+--------+
|xyz |item1Id|1.0 |pgroup1 |X |100 |prod1Id |A |10 |1 |
|xyz |item2Id|0.5 |pgroup1 |X |100 |prod2Id |B |20 |3 |
+---------+-------+-----+--------------+-----------------+-------------------+---------+------------+--------------+--------+
In essence, in addition to unnesting the individual struct arrays, I need to join them with each other based on some keys (above: items with products using productId, as well as products with productGroups using productGroupId).
I've tried tinkering around a bit with explode:
df_exploded = df.withColumn("items_exploded", explode("items"))
df_exploded = df_exploded.withColumn("products_exploded", explode("products"))
df_exploded = df_exploded.withColumn("productGroups_exploded", explode("productGroups"))
df_exploded = df_exploded.drop('items').drop('productGroups').drop('products')
df_exploded.show(truncate=False)
+---------+--------------------------+-------------------------+----------------------+
|invoiceId|items_exploded |products_exploded |productGroups_exploded|
+---------+--------------------------+-------------------------+----------------------+
|xyz |{item1Id, 1.0, prod1Id, 1}|{A, 10, pgroup1, prod1Id}|{X, 100, pgroup1} |
|xyz |{item1Id, 1.0, prod1Id, 1}|{B, 20, pgroup1, prod2Id}|{X, 100, pgroup1} |
|xyz |{item2Id, 0.5, prod2Id, 3}|{A, 10, pgroup1, prod1Id}|{X, 100, pgroup1} |
|xyz |{item2Id, 0.5, prod2Id, 3}|{B, 20, pgroup1, prod2Id}|{X, 100, pgroup1} |
+---------+--------------------------+-------------------------+----------------------+
But it's not quite what I need. Specifically, I've still got the JSON data in several columns which I'm not sure how to join efficiently on.
I've also tried playing around with json_tuple, but that requires all the values to be strings: in my case they are not.
I've already looked at various other questions such as this one, but unfortunately they don't quite conform to my use-case seeing how I've got several nested, related array items in no specific order.
I'm kind of lost as to how to do this in an efficient manner using something like groupBy. I'm not even sure what to call this kind of unnesting operation (unnest + join) so as to try searching a bit more.
Any help would be greatly appreciated.