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