SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Video Indexer のポータルから DL した JSON を SQL Server でクエリをかけてみる

leave a comment

Microsoft Video Indexer expands functionality unlocking more video insights に記載されていますが、9 月の更新で、Video Indexer の Insights の情報をポータルからダウンロードできるようになりました。

image

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 の操作については次の情報を参考にするとよいかと。

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

これで次のように、音声認識されたテキストを取得することができます。

image

Written by masayuki.ozawa

9月 24th, 2017 at 6:30 pm

Leave a Reply

*