SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for AWS Amazon Web Services, Redshift, AWS Lambda Functions, S3 Buckets, VPC, EC2, IAM

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"}';

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

 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;

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
  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 <> ''

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.


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.