Parse JSON Response of Amazon Transcribe Service using SQL OpenJSON
Amazon Web Services AWS provides Amazon Transcribe service for developers to convert speech to text. While I was developing a prototype application using Amazon Transcribe service, I tried to parse JSON response of the AWS service in SQL Server with the latest enhancements of SQL Server 2017 to support JSON data handling.
If you are new to cloud computing and interested on AWS Amazon Web Services solutions for speech to text applications, you can visit Transcribe service at Amazon AWS platform.
When a transcription job is created on AWS Transcribe service, a TranscriptFileUri is returned back to the user. If the URL provided by TranscriptFileUri is called, a JSON data similar to following example is returned.
{"jobName":"transcription-job-test01","accountId":"777999555111","results":{"transcripts":[{"transcript":"Hello, my name is joanna. Nice to meet you."}],"items":[{"start_time":"0.000","end_time":"0.640","alternatives":[{"confidence":"1.0000","content":"Hello"}],"type":"pronunciation"},{"alternatives":[{"content":","}],"type":"punctuation"},{"start_time":"0.740","end_time":"0.900","alternatives":[{"confidence":"1.0000","content":"my"}],"type":"pronunciation"},{"start_time":"0.900","end_time":"1.120","alternatives":[{"confidence":"1.0000","content":"name"}],"type":"pronunciation"},{"start_time":"1.120","end_time":"1.250","alternatives":[{"confidence":"1.0000","content":"is"}],"type":"pronunciation"},{"start_time":"1.250","end_time":"1.870","alternatives":[{"confidence":"1.0000","content":"joanna"}],"type":"pronunciation"},{"alternatives":[{"content":"."}],"type":"punctuation"},{"start_time":"1.880","end_time":"2.200","alternatives":[{"confidence":"1.0000","content":"Nice"}],"type":"pronunciation"},{"start_time":"2.200","end_time":"2.310","alternatives":[{"confidence":"0.9992","content":"to"}],"type":"pronunciation"},{"start_time":"2.310","end_time":"2.520","alternatives":[{"confidence":"0.9833","content":"meet"}],"type":"pronunciation"},{"start_time":"2.520","end_time":"2.830","alternatives":[{"confidence":"0.9831","content":"you"}],"type":"pronunciation"},{"alternatives":[{"content":"."}],"type":"punctuation"}]},"status":"COMPLETED"}
Above JSON file is the transcript of a speech file which is located in a S3 bucket.
Using Amazon Transcribe, developers can build speech-to-text applications easily.
Below code is showing how I declared SQL variable for JSON data for this SQL tutorial.
declare @json nvarchar(max)
set @json = N'
{"jobName":"transcription-job-test01","accountId":"777999555111","results":{"transcripts":[{"transcript":"Hello, my name is joanna. Nice to meet you."}],"items":[{"start_time":"0.000","end_time":"0.640","alternatives":[{"confidence":"1.0000","content":"Hello"}],"type":"pronunciation"},{"alternatives":[{"content":","}],"type":"punctuation"},{"start_time":"0.740","end_time":"0.900","alternatives":[{"confidence":"1.0000","content":"my"}],"type":"pronunciation"},{"start_time":"0.900","end_time":"1.120","alternatives":[{"confidence":"1.0000","content":"name"}],"type":"pronunciation"},{"start_time":"1.120","end_time":"1.250","alternatives":[{"confidence":"1.0000","content":"is"}],"type":"pronunciation"},{"start_time":"1.250","end_time":"1.870","alternatives":[{"confidence":"1.0000","content":"joanna"}],"type":"pronunciation"},{"alternatives":[{"content":"."}],"type":"punctuation"},{"start_time":"1.880","end_time":"2.200","alternatives":[{"confidence":"1.0000","content":"Nice"}],"type":"pronunciation"},{"start_time":"2.200","end_time":"2.310","alternatives":[{"confidence":"0.9992","content":"to"}],"type":"pronunciation"},{"start_time":"2.310","end_time":"2.520","alternatives":[{"confidence":"0.9833","content":"meet"}],"type":"pronunciation"},{"start_time":"2.520","end_time":"2.830","alternatives":[{"confidence":"0.9831","content":"you"}],"type":"pronunciation"},{"alternatives":[{"content":"."}],"type":"punctuation"}]},"status":"COMPLETED"}
'
It is always a good idea to execute a simple SQL SELECT statement. As seen in the output of the SQL query, text data is fetched with type equals to 1 and JSON data is fetched having value 5 in type column.
select * from openJSON(@json)
Executing SQL OpenJSON using WITH clausing including return data types and names will convert this row based output into a column based view just like SQL PIVOT queries.
select *
from openJSON(@json) with (
jobName nvarchar(100),
accountId varchar(30),
results nvarchar(max),
[status] varchar(30)
) as j
As seen in following screenshot of the outcome, everything is OK except the results column.
There is no value returned for results, it is NULL.
But if you remember from the previous SQL OpenJSON query, the row with "key" column value "results" has a JSON data in its "value" column. Please note that the same row has "type" column with 5
Please keep in mind, for data that includes JSON data and has type 5 or 4 can be processed as follows with a slight change only by adding "as JSON" after data type declaration in WITH clause.
Here is the corrected OpenJSON SQL query
select *
from openJSON(@json) with (
jobName nvarchar(100),
accountId varchar(30),
results nvarchar(max) as json,
[status] varchar(30)
) as j
And the output is as follows
After modifying SQL Select query to parse JSON string on SQL Server using OpenJSON, we have a new question.
How to parse the JSON string in "results" column and combine with other fields like jobName, etc.
Here comes the cross apply join type for our requirement.
Database developers can use a second OpenJSON in combination with CROSS APPLY as in below SQL code block
select *
from openJSON(@json) with (
jobName nvarchar(100),
accountId varchar(30),
results nvarchar(max) as json,
[status] varchar(30)
) as j
cross apply openJSON(results)
As database developers will see on "key" column, the JSON string in "result" column has JSON objects with name "transcripts" and "items".
You can understand the JSON string on these columns from their data types indicated with 4.
Remember, data types with 4 and 5 can be queried with "CROSS APPLY"
Before executing a Transact-SQL query to parse "results" data, let's convert above output by applying a pivot conversion.
select
jobName, accountId, [status], r.*
from openJSON(@json) with (
jobName nvarchar(100),
accountId varchar(30),
results nvarchar(max) as json,
[status] varchar(30)
) as j
cross apply openJSON(results) with (
transcripts nvarchar(max) as json,
items nvarchar(max) as json
) as r
As seen in the output, we have now transacript column and items column in the output columns list
SQL Server programmers will see that the output has still JSON data in query outputs.
The solution is same as above.
Using CROSS APPLY with OpenJSON and in output column list defined using WITH clause use AS JSON for Json string containing columns.
Following SQL Server OpenJSON query will return the JSON string response of Amazon Transcribe service call in tabular format.
select
ROW_NUMBER() over (order by getdate()) as rn,
jobName,
accountId,
[status],
transcript,
i.start_time,
i.end_time,
i.[type],
a.confidence,
a.content
from openJSON(@json) with (
jobName nvarchar(100),
accountId varchar(30),
results nvarchar(max) as json,
[status] varchar(30)
) as j
cross apply openJSON(results) with (
transcripts nvarchar(max) as json,
items nvarchar(max) as json
) as r
cross apply openJSON(transcripts) with (
transcript nvarchar(max)
) as t
cross apply openJSON(items) with (
start_time varchar(10),
end_time varchar(10),
alternatives nvarchar(max) as json,
[type] varchar(20)
) as i
cross apply openJSON(alternatives) with (
confidence decimal(10,5),
content nvarchar(100)
) as a
The JSON string output is converted into tabular format by executing above SQL OpenJSON query on a SQL Server 2017 database.
