Databricks Community

NathanSundarara · ‎05-11-2023

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

Anonymous · ‎05-13-2023

@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.

Anonymous · ‎05-22-2023

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!