その他のJSONフォーマットの取り扱い
以前のJSONデータの読み込みの例では、JSONEachRow
(NDJSON
) の使用を前提としています。このフォーマットは、各JSON行のキーをカラムとして読み込みます。例えば:
このフォーマットは一般的にJSONで最もよく使用される形式ですが、ユーザーは他の形式に出会ったり、JSONを単一オブジェクトとして読み取る必要があります。
以下に、他の一般的な形式でのJSONの読み込みとロードの例を示します。
JSONをオブジェクトとして読み込む
これまでの例は、JSONEachRow
が改行区切りのJSONを読み込み、各行がテーブルの行にマッピングされ、各キーがカラムに対応する方法を示しています。これは、JSONが予測可能で各カラムのタイプが単一である場合に理想的です。
対照的に、JSONAsObject
は各行を単一の JSON
オブジェクトとして扱い、それを JSON
型の単一カラムに保存します。これにより、ネストされたJSONペイロードや、キーが動的で潜在的に複数のタイプを持つ場合により適しています。
JSONEachRow
を行単位の挿入用として使用し、柔軟または動的なJSONデータを格納する際には JSONAsObject
を使用してください。
前述の例と対照的に、以下のクエリでは同じデータを1行のJSONオブジェクトとして読み取ります:
JSONAsObject
フォーマットは、単一のJSONオブジェクトカラムを使用してテーブルに行を挿入するのに便利です。例:
JSONAsObject
フォーマットは、オブジェクトの構造が不一致な場合の改行区切りJSONを読み取るのにも役立ちます。例えば、キーが行ごとに型が変わる(時には文字列、時にはオブジェクトになる)場合です。そのような場合、ClickHouseは JSONEachRow
を使用して安定したスキーマを推測できず、JSONAsObject
により厳密な型制約なしでデータを取り込むことができ、各JSON行を単一のカラムに全体として保存します。以下の例では JSONEachRow
が失敗することに注意してください:
逆に、JSONAsObject
はこの場合に使用でき、JSON
型は同じサブカラムに対して複数の型をサポートします。
JSONオブジェクトの配列
JSONデータの最も一般的な形式の一つは、JSON配列内にJSONオブジェクトのリストを持つことです。この例を見てみましょう。この例:
このようなデータのためのテーブルを作成しましょう:
JSONオブジェクトのリストをインポートするには、JSONEachRow
フォーマットを使用します(list.json ファイルからデータを挿入します):
ローカルファイルからデータをロードするために FROM INFILE 節を使用しました。インポートが成功したことが確認できます:
JSONオブジェクトのキー
場合によっては、JSONオブジェクトのリストが配列要素ではなくオブジェクトプロパティとしてエンコードされることがあります(例えば、objects.json を見てください):
ClickHouseは、この種のデータから読み込むためにJSONObjectEachRow
フォーマットを使用できます:
親オブジェクトキーの値を指定する
親オブジェクトキーの値もテーブルに保存したいとします。その場合、以下のオプションを使用してキーの値を保存するカラムの名前を定義できます:以下のオプション:
現在、file()
関数を使用して元のJSONファイルから読み込まれるデータを確認できます:
id
カラムがキー値で正しく埋め込まれていることに注意してください。
JSON配列
時には、スペースを節約するために、JSONファイルがオブジェクトの代わりに配列でエンコードされます。この場合、JSON配列のリストを扱います:
この場合、ClickHouseはこのデータをロードし、配列内の順序に基づいて各値を対応するカラムに割り当てます。これにはJSONCompactEachRow
フォーマットを使用します:
JSON配列から個別カラムをインポートする
場合によっては、データが行単位ではなくカラム単位でエンコードされることがあります。この場合、親JSONオブジェクトには値を持つカラムが含まれています。以下のファイルを見てみましょう:
ClickHouseはJSONColumns
フォーマットを使用してそのようなデータを解析します:
カラムの配列を扱う際には、JSONCompactColumns
フォーマットを使用することもできます:
JSONオブジェクトを解析するのではなく保存する
JSONオブジェクトを解析するのではなく、単一の String
(または JSON
) カラムに保存したい場合があります。これは、異なる構造のJSONオブジェクトのリストを扱う際に便利です。このファイルを例に取りますが、親リスト内に複数の異なるJSONオブジェクトがあります:
次のテーブルに元のJSONオブジェクトを保存したいとします:
このテーブルにファイルからデータをロードするために、JSONAsString
フォーマットを使用してJSONオブジェクトを解析せずに保持します:
そして、保存されたオブジェクトをクエリするためにJSON関数を使用できます:
JSONAsString
は、通常 JSONEachRow
フォーマットで使用されるJSONオブジェクト・パー・ライン形式のファイルにおいても問題なく機能することに注意してください。
ネストされたオブジェクトのスキーマ
ネストされたJSONオブジェクト(例:list-nested.json)を扱う場合、明示的なスキーマを定義し、複雑な型 (Array
、Object Data Type
または Tuple
)を使用してデータをロードできます:
ネストされたJSONオブジェクトへのアクセス
ネストされたJSONキーに参照するには、以下の設定オプションを有効にします:
これにより、ドット記法を使用してネストされたJSONオブジェクトキーにアクセスできるようになります(機能させるためにはバックティック記号で囲むことを忘れないでください):
これにより、ネストされたJSONオブジェクトをフラット化したり、いくつかのネストされた値を別のカラムとして保存したりできます。
不明なカラムのスキップ
デフォルトでは、ClickHouseはJSONデータをインポートする際に不明なカラムを無視します。month
カラムなしで元のファイルをテーブルにインポートしてみましょう:
3カラムの元のJSONデータをこのテーブルに挿入できます:
ClickHouseはインポート時に不明なカラムを無視します。この挙動は、input_format_skip_unknown_fields 設定オプションで無効にできます:
ClickHouseは不一致なJSONとテーブルカラム構造のケースで例外をスローします。
BSON
ClickHouseは、BSON エンコードファイルからのエクスポートとインポートをサポートしています。このフォーマットは、MongoDB データベースなど、一部のDBMSで使用されます。
BSONデータをインポートするには、BSONEachRowフォーマットを使用します。以下のBSONファイルからデータをインポートします:
同じフォーマットを使用してBSONファイルへのエクスポートも行えます:
その後、データは out.bson
ファイルにエクスポートされます。