PySpark sqlContext JSON query all values of an array

Question

I currently have a json file that i am trying to query with sqlContext.sql() that looks something like this:

{
  "sample": {
    "persons": [
      {
        "id": "123",
      },
      {
        "id": "456",
      }
    ]
  }
}

If I just want the first value I would type:

sqlContext.sql("SELECT sample.persons[0] FROM test")

but I want all the values of "persons" without having to write a loop. Loops just consume too much processing power, and given the size of these files, that would just be impractical.

I thought I would be able to put a range in the [] brackets but I can't find any syntax by which to do that.

Community · Accepted Answer · 2017-05-23 12:19:19Z

3

If your schema looks like this:

root
 |-- sample: struct (nullable = true)
 |    |-- persons: array (nullable = true)
 |    |    |-- element: struct (containsNull = true)
 |    |    |    |-- id: string (nullable = true)

and want to access individual structs from persons array all you have to do is to explode it:

from pyspark.sql.functions import explode

df.select(explode("sample.persons").alias("person")).select("person.id")

See also: Querying Spark SQL DataFrame with complex types

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Apr 7, 2016 at 22:13

zero323

331k108 gold badges982 silver badges958 bronze badges

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

Collectives™ on Stack Overflow

PySpark sqlContext JSON query all values of an array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related