topic Re: Requesting help for Parsing JSON using Spark SQL. in DELETE

Requesting help for Parsing JSON using Spark SQL.

NathanSundarara — Thu, 11 May 2023 14:22:26 GMT

Hello this is my first post and we are started using databricks. I'm testing POC I'm more familiar with SQL syntax than python. Need some help with parsing the json. Here is a sample json.

The JSON parsing using lateral view , explode with SQL documentation is not sufficient any help from the community will help me move further.

I attached the json as a file.

Thanks in Advance

Re: Requesting help for Parsing JSON using Spark SQL.

Anonymous — Sat, 13 May 2023 16:28:26 GMT

@Nathan Sundararajan :

Thanks for sending over the json.

Here's an example of how you can parse the JSON using lateral view and explode in SQL:

Let's say you have a JSON data stored in a table called "json_data", and you want to extract the following fields from the JSON: "id", "name", "age", "address.city", and "address.state".

Here's the SQL query:

SELECT 
  json_extract_scalar(json_data, '$.id') as id,
  json_extract_scalar(json_data, '$.name') as name,
  json_extract_scalar(json_data, '$.age') as age,
  json_extract_scalar(json_data, '$.address.city') as city,
  json_extract_scalar(json_data, '$.address.state') as state
FROM 
  json_table(
    '[
      {
        "id": 1,
        "name": "John Doe",
        "age": 35,
        "address": {
          "city": "New York",
          "state": "NY"
        }
      },
      {
        "id": 2,
        "name": "Jane Smith",
        "age": 28,
        "address": {
          "city": "San Francisco",
          "state": "CA"
        }
      }
    ]',
    '$[*]'
    COLUMNS (
      json_data VARCHAR(4000) PATH '$'
    )
  ) jt

In this query, we are using the "json_table" function to convert the JSON array into rows. The "json_data" column contains the entire JSON data for each row.

Then, we use the "json_extract_scalar" function to extract the specific fields from the JSON using their respective JSON paths. The output of this query will be a table with the following columns: "id", "name", "age", "city", and "state", where each row represents an object in the JSON array.

Re: Requesting help for Parsing JSON using Spark SQL.

Anonymous — Mon, 22 May 2023 07:16:00 GMT

Hi @Nathan Sundararajan

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.

We'd love to hear from you.

Thanks!

Re: Requesting help for Parsing JSON using Spark SQL.

NathanSundarara — Mon, 22 May 2023 12:43:26 GMT

The answer provided was not like my example 😞 In another post I asked more specific and didn't get answer. So it's not resolved.

Re: Requesting help for Parsing JSON using Spark SQL.

NathanSundarara — Mon, 22 May 2023 12:44:38 GMT

@Vidula Khanna please see my question

Feed Detail (databricks.com)