PySpark - Convert list of JSON objects to rows

Question

I want to convert a list of objects and store their attributes as columns.

{
  "heading": 1,
  "columns": [
    {
      "col1": "a",
      "col2": "b",
      "col3": "c"
    },
    {
      "col1": "d",
      "col2": "e",
      "col3": "f"
    }
  ]
}

Final Result

heading | col1 | col2 | col3
1       | a    | b    | c
1       | d    | e    | f

I am currently flattening my data (and excluding the columns column)

df = target_table.relationalize('roottable', temp_path)

However, for this use case, I will need the columns column. I saw examples where arrays_zip and explode was used. Would I need to iterate through each object or is there an easier way to extract each object and convert into a row?

what is the datatype for columns? string or array of struct? — jxc
– jxc, Commented Nov 12, 2019 at 17:35
columns is array of struct (with the attributes being strings) — nsc060
– nsc060, Commented Nov 12, 2019 at 17:55

jxc · Accepted Answer · 2019-11-12 18:08:46Z

2

use Spark SQL builtin function: inline or inline_outer is probably easiest way to handle this (use inline_outer when NULL is allowed in columns):

From Apache Hive document:

Explodes an array of structs to multiple rows. Returns a row-set with N columns (N = number of top level elements in the struct), one row per struct from the array. (As of Hive 0.10.)

df.selectExpr('heading', 'inline_outer(columns)').show()                                                           
+-------+----+----+----+
|heading|col1|col2|col3|
+-------+----+----+----+
|      1|   a|   b|   c|
|      1|   d|   e|   f|
+-------+----+----+----+

answered Nov 12, 2019 at 18:08

jxc

14k4 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PySpark - Convert list of JSON objects to rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related