Microsoft Video Indexer expands functionality unlocking more video insights に記載されていますが、9 月の更新で、Video Indexer の Insights の情報をポータルからダウンロードできるようになりました。
JSON に含まれている内容については、API 経由で取得できる情報と同じかと思いますので、Examine the Video Indexer output から、取得できる内容を確認できるかと。
Insights の情報が含まれている JSON のファイルをダウンロードすることができるようになっているのですが、
これを SQL Server 上でクエリをかけてみたいと思います。
最初に JSON のファイルを nvarchar(max) に展開をします。
展開には次のようなクエリを実行してみます。
SQL Server のローカルに格納されているBULK INSERT を実行しています。
OPENROWSET だとうまく開けなかったため、一時テーブルに入れてから本テーブルに移すような処理となっています。
DROP TABLE IF EXISTS [VideoIndexer] CREATE TABLE [dbo].[VideoIndexer]( [Name] [nvarchar](400) NULL, [Translation] [nvarchar](max) NULL ) GO DROP TABLE IF EXISTS #JsonTbl CREATE TABLE #JsonTbl (C1 nvarchar(max)) BULK INSERT #JsonTbl FROM 'C:\temp\decode2015.json' WITH (CODEPAGE='65001' ) INSERT INTO VideoIndexer SELECT 'de:code 2015', C1 FROM #JsonTbl SELECT * FROM VideoIndexer
これで、nvarchar(max) に JSON をそのまま格納することができましたので、次に音声情報を文字起こしされたものを抜き出してみたいと思います。
SQL Server 2016 で追加された JSON の操作を組み合わせています。
JSON の操作については次の情報を参考にするとよいかと。
- Advanced JSON Techniques in SQL Server 2016 – Part 3
- OPENJSON (TRANSACT-SQL)
- JSON_VALUE (TRANSACT-SQL)
- JSON_QUERY (TRANSACT-SQL)
- SELECT の FOR 句 (TRANSACT-SQL)
DECLARE @indexername nvarchar(100) = N'de:code 2015'
SELECT
JSON_VALUE(T2.value, 'lax $.timeRange.start') AS [start],
JSON_VALUE(T2.value, 'lax $.timeRange.end') AS [end],
JSON_VALUE(T2.value, 'lax $.text') AS [TranslateText]
FROM OPENJSON(
(SELECT
JSON_QUERY(value, '$.insights.transcriptBlocks')
FROM
OPENJSON(
(SELECT
JSON_QUERY(value, 'lax $')
FROM
OPENJSON( (SELECT Translation FROM VideoIndexer WHERE Name = @indexername) )
WHERE [key] = 'breakdowns')
)
)
) AS T1
CROSS APPLY
OPENJSON(
JSON_QUERY(T1.value, '$.lines')
) AS T2
これで次のように、音声認識されたテキストを取得することができます。
