Scribd
Upload
4 views1 page

Querying Nested JSON Data in SQL

The document contains SQL queries for extracting and manipulating nested data from a JSON file in a database. It demonstrates how to select specific fields, handle arrays, and perform unions on the data. The queries focus on retrieving job-related information and previous company details from the JSON structure.

Uploaded by

techgeek0069
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views1 page

Querying Nested JSON Data in SQL

The document contains SQL queries for extracting and manipulating nested data from a JSON file in a database. It demonstrates how to select specific fields, handle arrays, and perform unions on the data. The queries focus on retrieving job-related information and previous company details from the JSON structure.

Uploaded by

techgeek0069
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

// Handling nested data

SELECT RAW_FILE:job as job FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

SELECT
RAW_FILE:[Link]::INT as salary
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

SELECT
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:[Link]::INT as salary,
RAW_FILE:[Link]::STRING as title
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

// Handling arreys

SELECT
RAW_FILE:prev_company as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

SELECT
RAW_FILE:prev_company[1]::STRING as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

SELECT
ARRAY_SIZE(RAW_FILE:prev_company) as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW;

SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:prev_company[0]::STRING as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
UNION ALL
SELECT
RAW_FILE:id::int as id,
RAW_FILE:first_name::STRING as first_name,
RAW_FILE:prev_company[1]::STRING as prev_company
FROM OUR_FIRST_DB.PUBLIC.JSON_RAW
ORDER BY id

You might also like