Updated March 8, 2023
Definition on Redshift JSON
Redshift JSON has limited support while working with the JSON documents in redshift, basically, there are three types of options available in redshift to load the data into table. First option is we can convert the JSON file into relational model before loading data into the redshift, to load the data using this options we need to create the relational target database. Second option is load all the JSON documents in redshift table and query those documents using JSON functions, there are multiple JSON function available in redshift to query the data of JSON documents.
Syntax
Below is the syntax of JSON in redshift are as follows.
1) Select json_function (name_of_json_column, ‘value of json column’) where condition
2) Select json_function (name_of_json_column,) group by, order by
Parameter description syntax of redshift JSON.
1) JSON function – This is the function which was we have using with JSON data to retrieve from JSON column. There are multiple JSON function available in redshift to query the JSON data. We can retrieve the JSON column data using JSON function in redshift.
2) Select – Select command is used with JSON function to retrieve data from table by using the clauses and conditional operator.
3) Name of column – This is the name of JSON data column which was we have using with JSON function to retrieve data from table.
4) Value of json column – This is nothing but the column value which was we have using to segregate the JSON document data in redshift. We can segregate the data from table column as per value which was we have used in our query.
5) Where condition – We can retrieve JSON document from column by using where condition in redshift.
6) Order by condition – We can retrieve JSON document from column by using order by condition in redshift.
7) Group by condition – We can retrieve JSON document from column by using group by condition in redshift.
How JSON works in Redshift?
There are multiple options available to load the JSON documents in redshift. After loading the data we can retrieve the JSON data by using following JSON functions.
1) Is valid JSON array (IS_VALID_JSON_ARRAY) function.
2) Is valid JSON (IS_VALID_JSON) function.
3) Json serialize (JSON_SERIALIZE) function.
4) Json parse (JSON_PARSE) function.
5) Json extract path text (JSON_EXTRACT_PATH_TEXT) function.
6) Json extract array element text (JSON_EXTRACT_ARRAY_ELEMENT_TEXT) function.
- If we want to store the small number of key-value pairs then JSON document is best suited for the same. Using JSON format we can save the storage space of storing the data.
- We can store multiple key value pair in a single column by using JSON format, we cannot stored multiple key-value pair in other format.
- To use the JSON function on integer datatype values or the data which was not in JSON format. We can apply JSON function only on JSON type of document.
Below example shows that we can apply JSON function only on JSON type of columns.
Code:
Select json_extract_path_text (stud_name, 'A') as key2 from redshift_json where stud_id = 101;
- In above example, we have applied JSON function on stud_name column and trying to retrieve key-value pair as “A”, But it will showing error as invalid JSON object which was we have used in our query.
- Also, it will showing the parsing error of query.
- We cannot use the integer datatype column with JSON function in redshift, we need to use only JSON type of data.
Below example shows that we cannot use the integer datatype of column with JSON function in redshift.
Code:
Select json_extract_path_text (stud_id) from redshift_json where stud_id = 101;
- In above example, we have used column name as stud_id with JSON function, stud_id datatype as integer. So it will issues the error like integer does not exist, which was not found any matching function or arguments.
- We can use copy command to load the data from JSON file to redshift table. We can also use the JSON files which was stores in the S3 bucket.
- We can also copy JSON file fields automatically by using option as auto or we need to specify the path of JSON file.
Examples
Below is the example of JSON in redshift are as follows.
1) Querying JSON fields using IS_VALID_JSON function
The below example shows querying JSON fields using IS_VALID_JSON function are as follows. This function is validates the JSON string.
In below example, we have used JSON column to validate the JSON data from function. We have not found any invalid JSON data in JSON column.
Code:
Select stud_id, json, is_valid_json (json) from redshift_json order by stud_id;
2) Querying JSON fields using is_valid_json_array function
- Below example shows querying JSON fields using is_valid_json_array function are as follows. This function validates the JSON array string.
- In below example, we have used JSON column to validate the JSON array value from function. We have not found any JSON array in JSON column.
Code:
Select stud_id, json, is_valid_json_array (json) from redshift_json order by stud_id;
3) Querying JSON fields using json_extract_path_text function
- Below example shows querying JSON fields using json_extract_path_text function are as follows. This function is extracting the value from the text.
- In below example, we have used json column to extract path text data from function.
Code:
Select stud_id, json, json_extract_path_text (json, 'key2') as json_key from redshift_json order by stud_id;
4) Querying JSON fields using json_parse function
- Below example shows querying JSON fields using json_parse function are as follows. This function is used to parse the JSON value.
- In below example, we have used json column to parse the data from function.
Code:
Select stud_id, json, json_parse (json) as json_key from redshift_json order by stud_id;
Conclusion
We can use multiple JSON function to query data from table columns. Redshift JSON is very useful and important to store the value in key-value pairs. Using JSON we can store multiple column value within a single column. We can also minimize the storage usage using JSON in redshift.
Recommended Articles
This is a guide to Redshift JSON. Here we discuss the definition, syntax, How JSON works in Redshift? examples with code implementation respectively. You may also have a look at the following articles to learn more –
