How to find age greater than value from json using pyspark?

Question

I am new in pyspark. Can you please help me how to get max age from json using pyspark? I tried df.filter(df['employees.age'] > 22).show() It throws error,

org.apache.spark.sql.AnalysisException: cannot resolve '(employees.age > 22)' due to data type mismatch: differing types in '(employees.age > 22)' (array and int).;; 'Filter (employees#0.age > 22)

{'employees': [{'age': '12', 'firstName': 'John', 'lastName': 'Doe'},
  {'age': '14', 'firstName': 'Anna', 'lastName': 'Smith'},
  {'age': '54', 'firstName': 'Peter1', 'lastName': 'Jones1'},
  {'age': '44', 'firstName': 'Peter2', 'lastName': 'Jones2'},
  {'age': '42', 'firstName': 'Peter3', 'lastName': 'Jones3'},
  {'age': '62', 'firstName': 'Peter4', 'lastName': 'Jones4'},
  {'age': '65', 'firstName': 'Peter5', 'lastName': 'Jones5'},
  {'age': '23', 'firstName': 'Peter6', 'lastName': 'Jones6'},
  {'age': '77', 'firstName': 'Pete7', 'lastName': 'Jones7'},
  {'age': '82', 'firstName': 'Peter8', 'lastName': 'Jones8'},
  {'age': '92', 'firstName': 'Peter9', 'lastName': 'Jones9'},
  {'age': '78', 'firstName': 'Peter10', 'lastName': 'Jones10'}]}

I want to find those employee who has age greater than 22.

Other than that, your query isn't wrong. The age is actually in an array, Please [edit] your question to include the JSON. — OneCricketeer, Mar 22 '18 at 13:20
https://github.com/apache/spark/blob/master/examples/src/main/python/sql/basic.py See in this file df.filter(df['employees.age'] > 22).show() — Prashant Patel, Mar 22 '18 at 13:23
just copy paste the output of `df.show(5, truncate=False)` and `df.printSchema()` and update the question — Ramesh Maharjan, Mar 22 '18 at 13:23
http://spark.apache.org/docs/2.2.0/api/python/pyspark.sql.html#pyspark.sql.GroupedData.max — OneCricketeer, Mar 22 '18 at 13:24
Yeah. I see it. And that's not finding a max. It's removing anything less than 22. We can't accurately help you if you don't [edit] your question to include the JSON data — OneCricketeer, Mar 22 '18 at 13:26
[[12, John, Doe], [14, Anna, Smith], [54, Peter1, Jones1], [44, Peter2, Jones2], [42, Peter3, Jones3], [62, Peter4, Jones4], [65, Peter5, Jones5], [23, Peter6, Jones6], [77, Pete7, Jones7], [82, Peter8, Jones8], [92, Peter9, Jones9], [78, Peter10, Jones10]]| root |-- employees: array (nullable = true) | |-- element: struct (containsNull = true) | | |-- age: string (nullable = true) | | |-- firstName: string (nullable = true) | | |-- lastName: string (nullable = true) — Prashant Patel, Mar 22 '18 at 13:44
I second the CV. @PrashantPatel please read [ask] and [how to create good reproducible apache spark dataframe examples](https://stackoverflow.com/questions/48427185/how-to-make-good-reproducible-apache-spark-dataframe-examples). We really do want to help, but it's hard to do without a [mcve]. — pault, Mar 22 '18 at 14:02
@ Prashant Patel if u r in starting phase then plz read this url http://spark.apache.org/docs/latest/sql-programming-guide.html#untyped-dataset-operations-aka-dataframe-operations — Rajnish Kumar, Mar 22 '18 at 14:15

score 0 · Answer 1 · edited Mar 22 '18 at 15:05

0

It look like You have a list (or array) in employees.age. Even if you have only 1 item in that array. Try use that in your code

"filter(employees#0.age > 22)"

edited Mar 22 '18 at 15:05

mayank agrawal

2,495
2
13
32

answered Mar 22 '18 at 13:20

Aloha

11
1

score 0 · Accepted Answer · answered Mar 22 '18 at 14:09

Spark doesn't handle multiple line data very well.

The Example code you linked to shows how to need to lay out the file in the examples folder.

Single objects on multiple lines. Not one object with an array

{'age': '54', 'firstName': 'Peter1', 'lastName': 'Jones1'}
{'age': '44', 'firstName': 'Peter2', 'lastName': 'Jones2'}
{'age': '42', 'firstName': 'Peter3', 'lastName': 'Jones3'}

Also, JSON uses double quotes for keys and values, so you'll need to fix that

Then

df = spark.read().json("file.json")

And, to find the max age

df.groupBy().max("age").show()

How to find age greater than value from json using pyspark?

2 Answers2