In SparkSQL how could I select a subset of columns from a nested struct and keep it as a nested struct in the result using SQL statement?

user_2833774 0
Viewed 96

I can do the following statement in SparkSQL:

result_df = spark.sql("""select
    one_field,
    field_with_struct
  from purchases""")

And resulting data frame will have the field with full struct in field_with_struct.

one_field field_with_struct
123 {name1,val1,val2,f2,f4}
555 {name2,val3,val4,f6,f7}

I want to select only few fields from field_with_struct, but keep them still in struct in the resulting data frame. If something could be possible (this is not real code):

result_df = spark.sql("""select
    one_field,
    struct(
      field_with_struct.name,
      field_with_struct.value2
    ) as my_subset
  from purchases""")

To get this:

one_field my_subset
123 {name1,val2}
555 {name2,val4}

Is there any way of doing this with SQL? (not with fluent API)

pyspark apache-spark-sql
1 Answers

In fact, the pseudo code which I have provided is working. For a nested array of object it's not so straightforward. At first, the array should be exploded (EXPLODE() function) and then selected a subset. After that it's possible to make a COLLECT_LIST().

WITH
  unfold_by_items AS (SELECT id, EXPLODE(Items) AS item FROM spark_tbl_items)
, format_items as (SELECT
    id
    , STRUCT(
              item.item_id
            , item.name
        ) AS item
    FROM unfold_by_items)
, fold_by_items AS (SELECT id, COLLECT_LIST(item) AS Items FROM format_items GROUP BY id)

SELECT * FROM fold_by_items

This will choose only two fields from the struct in Items and in the end returns a dataset which contains again an array with Items.

user_28337740
Related