Redshift Database JSON Parse Sample SQL Query

This Amazon Redshift database tutorial shows how Redshift database JSON json_extract_path_text function can be used to parse and extract attributes from JSON string stored in a table column.

Redshift JSON SQL Query

Let's create a Redshift database table using following SQL DDL statement and populate database table with sample data.


create table Expertise (

 id int, "data" varchar(max)

);



insert into Expertise select 1, '{"user":"Eralper","title":"solution architect","expertise":"databases, sap, aws"}';

insert into Expertise select 2, '{"user":"Darth Vader","title":"Sith Lord","expertise":"force"}';

Code

Redshift database SQL developers can query sample table data and parse JSON stored in table column data using json_extract_path_text SQL JSON function


select

 id,

 json_extract_path_text("data", 'user') as "user",

 json_extract_path_text("data", 'title') as "title",

 json_extract_path_text("data", 'expertise') as "expertise"

from Expertise;

Code

Output of above SQL Select statement is as follows

Redshift SQL JSON parse query sample

Of course, it is also possible to split expertise data and fetch each separate topic from the delimited list.
Here is a sample SQL to split by using split_part SQL string function on Redshift database.


with CTE as

(

 select

  id,

  json_extract_path_text("data", 'user') as "user" ,

  json_extract_path_text("data", 'title') as "title",

  json_extract_path_text("data", 'expertise') as "expertise"

 from Expertise

), Split as (

 select "user", title, split_part(expertise, ',', 1) as expertise from CTE

 union all

 select "user", title, split_part(expertise, ',', 2) as expertise from CTE

 union all

 select "user", title, split_part(expertise, ',', 3) as expertise from CTE

)

select * from split where expertise <> ''

Code

The output of our sample Redshift database SQL Select query where we parse JSON data column values and split them using a separator column is as follows

Redshift database parse JSON and split string SQL

In this Redshift database SQL query, we have seen a basic SQL code for parsing JSON data stored in a database table column by using json_extract_path_text function.

Just one last note for Amazon Redshift SQL programmers, to split string you can refer to Split String Delimited Data on Amazon Redshift Database using SQL tutorial.