その他の JSON フォーマットの扱い方

これまでの JSON データの読み込み例では、JSONEachRow（NDJSON）の利用を想定してきました。このフォーマットでは、各 JSON 行のキーを列として解釈します。例えば次のようになります。

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)
LIMIT 5

┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘

5 rows in set. Elapsed: 0.449 sec.

これは一般的に JSON で最もよく使われる形式ですが、他の形式に出会ったり、JSON 全体を 1 つのオブジェクトとして扱う必要が生じる場合もあります。

以下では、他の一般的な形式の JSON を読み取ったりロードしたりする例を示します。

JSON をオブジェクトとして読み込む

前の例では、JSONEachRow が改行区切りの JSON をどのように読み込み、各行をテーブルの 1 行に対応する個別のオブジェクトとして扱い、各キーを列に対応付けるかを示しました。これは、JSON の構造が予測可能で、各列に単一の型が対応しているケースに最適です。

これに対して JSONAsObject は、各行を 1 つの JSON オブジェクトとして扱い、型 JSON の単一列に格納します。このため、ネストされた JSON ペイロードや、キーが動的であり、かつ 1 つのキーに複数の型が存在しうるケースにより適しています。

行単位の挿入には JSONEachRow を使用し、柔軟または動的な JSON データを保存する場合は JSONAsObject を使用してください。

上記の例と比較すると、次のクエリでは同じデータを 1 行ごとに JSON オブジェクトとして読み取ります。

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONAsObject)
LIMIT 5

┌─json─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

5行のセット。経過時間: 0.338秒

JSONAsObject は、1 つの JSON オブジェクト型カラムだけを使ってテーブルに行を挿入する場合に便利です。例:

CREATE TABLE pypi
(
    `json` JSON
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO pypi SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONAsObject)
LIMIT 5;

SELECT *
FROM pypi
LIMIT 2;

┌─json─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
│ {"country_code":"CN","date":"2022-11-15","installer":"bandersnatch","project":"clickhouse-connect","python_minor":"","system":"","type":"bdist_wheel","version":"0.2.8"} │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.003 sec.

JSONAsObject フォーマットは、オブジェクトの構造が一貫していない場合の改行区切り JSON の読み取りにも有用です。例えば、あるキーの型が行ごとに異なる場合（あるときは文字列だが、別のときはオブジェクトであるなど）です。このようなケースでは、ClickHouse は JSONEachRow を使って安定したスキーマを推論できませんが、JSONAsObject を使うと、厳密な型の制約なしにデータを取り込むことができ、各 JSON 行をそのまま 1 つのカラムに保存できます。例えば、JSONEachRow が次の例でどのように失敗するかを確認してください。

SELECT count()
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/bluesky/file_0001.json.gz', 'JSONEachRow')

Elapsed: 1.198 sec.

サーバー (バージョン 24.12.1) から例外を受信しました: Code: 636. DB::Exception: sql-clickhouse.clickhouse.com:9440 から受信しました。DB::Exception: JSONEachRow 形式のファイルからはテーブル構造を抽出できません。エラー: Code: 117. DB::Exception: JSON オブジェクトにあいまいなデータがあります: 一部のオブジェクトではパス 'record.subject' の型が 'String' であり、別のものでは 'Tuple($type String, cid String, uri String)' になっています。パス 'record.subject' に対して String 型を使用するには、設定 input_format_json_use_string_type_for_ambiguous_paths_in_named_tuples_inference_from_objects を有効にできます。 (INCORRECT_DATA) (version 24.12.1.18239 (official build)) 構造推論のために読み取る行数やバイト数の上限を増やすには、設定 input_format_max_rows_to_read_for_schema_inference / input_format_max_bytes_to_read_for_schema_inference を使用してください。構造を手動で指定することもできます (ファイル/URI bluesky/file_0001.json.gz 内)。 (CANNOT_EXTRACT_TABLE_STRUCTURE)

 
逆に、`JSON`型は同一のサブカラムに対して複数の型をサポートするため、このケースでは`JSONAsObject`を使用できます。

```sql
SELECT count()
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/bluesky/file_0001.json.gz', 'JSONAsObject')

┌─count()─┐
│ 1000000 │
└─────────┘

1 row in set. Elapsed: 0.480 sec. Processed 1.00 million rows, 256.00 B (2.08 million rows/s., 533.76 B/s.)

JSON オブジェクトの配列

最も一般的な JSON データ形式の 1 つは、この例のように、JSON 配列の中に JSON オブジェクトのリストを持つ形式です。

> cat list.json
[
  {
    "path": "Akiba_Hebrew_Academy",
    "month": "2017-08-01",
    "hits": 241
  },
  {
    "path": "Aegithina_tiphia",
    "month": "2018-02-01",
    "hits": 34
  },
  ...
]

この種のデータを格納するテーブルを作成しましょう。

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

JSON オブジェクトのリストをインポートするには、JSONEachRow フォーマットを使用します（list.json ファイルからデータを挿入します）:

INSERT INTO sometable
FROM INFILE 'list.json'
FORMAT JSONEachRow

ローカルファイルからデータを読み込むために FROM INFILE 句を使用し、インポートが正常に完了したことを確認できます。

SELECT *
FROM sometable

┌─path──────────────────────┬──────month─┬─hits─┐
│ 1971-72_Utah_Stars_season │ 2016-10-01 │    1 │
│ Akiba_Hebrew_Academy      │ 2017-08-01 │  241 │
│ Aegithina_tiphia          │ 2018-02-01 │   34 │
└───────────────────────────┴────────────┴──────┘

JSON オブジェクトキー

場合によっては、JSON オブジェクトのリストを、配列の要素ではなくオブジェクトのプロパティとしてエンコードすることもできます（例については objects.json を参照してください）。

cat objects.json

{
  "a": {
    "path":"April_25,_2017",
    "month":"2018-01-01",
    "hits":2
  },
  "b": {
    "path":"Akahori_Station",
    "month":"2016-06-01",
    "hits":11
  },
  ...
}

ClickHouse は、この種のデータを JSONObjectEachRow フォーマットで読み込むことができます。

INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;
SELECT * FROM sometable;

┌─path────────────┬──────month─┬─hits─┐
│ Abducens_palsy  │ 2016-05-01 │   28 │
│ Akahori_Station │ 2016-06-01 │   11 │
│ April_25,_2017  │ 2018-01-01 │    2 │
└─────────────────┴────────────┴──────┘

親オブジェクトのキー値を指定する

テーブルに親オブジェクトのキー値も保存したいとします。この場合、キー値を保存する列名を定義するために、次のオプションを使用できます。

SET format_json_object_each_row_column_for_object_name = 'id'

次に、file() 関数を使用して、元の JSON ファイルからどのデータが読み込まれるかを確認できます。

SELECT * FROM file('objects.json', JSONObjectEachRow)

┌─id─┬─path────────────┬──────month─┬─hits─┐
│ a  │ April_25,_2017  │ 2018-01-01 │    2 │
│ b  │ Akahori_Station │ 2016-06-01 │   11 │
│ c  │ Abducens_palsy  │ 2016-05-01 │   28 │
└────┴─────────────────┴────────────┴──────┘

id 列にキーの値が正しく格納されていることに注目してください。

JSON 配列

場合によっては、容量を節約するために、JSON ファイルがオブジェクトではなく配列としてエンコードされていることがあります。この場合、JSON 配列のリストを扱うことになります。

cat arrays.json

["Akiba_Hebrew_Academy", "2017-08-01", 241],
["Aegithina_tiphia", "2018-02-01", 34],
["1971-72_Utah_Stars_season", "2016-10-01", 1]

この場合、ClickHouse はこのデータを読み込み、配列内での順序に従って各値を対応する列に割り当てます。これには JSONCompactEachRow フォーマットを使用します。

SELECT * FROM sometable

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

JSON 配列から個々のカラムをインポートする

場合によっては、データが行単位ではなく列単位でエンコードされていることがあります。この場合、親 JSON オブジェクトに、値が格納されたカラムが含まれます。次のファイルを参照してください。

cat columns.json

{
  "path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],
  "month": ["2016-07-01", "2015-07-01", "2015-07-01"],
  "hits": [178, 11, 1]
}

ClickHouse は、次のような形式のデータを解析するために、JSONColumns フォーマットを使用します。

SELECT * FROM file('columns.json', JSONColumns)

┌─path───────────────────────┬──────month─┬─hits─┐
│ 2007_Copa_America          │ 2016-07-01 │  178 │
│ Car_dealerships_in_the_USA │ 2015-07-01 │   11 │
│ Dihydromyricetin_reductase │ 2015-07-01 │    1 │
└────────────────────────────┴────────────┴──────┘

オブジェクトではなくカラムの配列を扱う場合には、JSONCompactColumns フォーマットを使用することで、よりコンパクトな形式も利用できます。

SELECT * FROM file('columns-array.json', JSONCompactColumns)

┌─c1──────────────┬─────────c2─┬─c3─┐
│ Heidenrod       │ 2017-01-01 │ 10 │
│ Arthur_Henrique │ 2016-11-01 │ 12 │
│ Alan_Ebnother   │ 2015-11-01 │ 66 │
└─────────────────┴────────────┴────┘

JSON オブジェクトをパースせずに保存する

場合によっては、JSON オブジェクトをパースせずに、単一の String（または JSON）カラムに保存したい場合があります。これは、構造が異なる複数の JSON オブジェクトのリストを扱う際に有用です。例として、親リスト内に複数の異なる JSON オブジェクトが含まれているこのファイルを見てみましょう。

cat custom.json

[
  {"name": "Joe", "age": 99, "type": "person"},
  {"url": "/my.post.MD", "hits": 1263, "type": "post"},
  {"message": "ディスク使用量の警告", "type": "log"}
]

元の JSON オブジェクトを次のテーブルに保存します。

CREATE TABLE events
(
    `data` String
)
ENGINE = MergeTree
ORDER BY ()

これで、ファイルからこのテーブルにデータを読み込む際に、JSON オブジェクトをパースせずそのまま保持するためのフォーマットとして JSONAsString を使用できます。

INSERT INTO events (data)
FROM INFILE 'custom.json'
FORMAT JSONAsString

保存されたオブジェクトに対してクエリを実行するには、JSON functions を使用できます。

SELECT
    JSONExtractString(data, 'type') AS type,
    data
FROM events

┌─type───┬─data─────────────────────────────────────────────────┐
│ person │ {"name": "Joe", "age": 99, "type": "person"}         │
│ post   │ {"url": "/my.post.MD", "hits": 1263, "type": "post"} │
│ log    │ {"message": "Warning on disk usage", "type": "log"}  │
└────────┴──────────────────────────────────────────────────────┘

JSONAsString は、1 行につき 1 つの JSON オブジェクトが含まれる形式のファイル（通常は JSONEachRow フォーマットとともに使用されます）の場合には、問題なく動作することに注意してください。

ネストされたオブジェクト用のスキーマ

ネストされた JSON オブジェクトを扱う場合には、明示的なスキーマをさらに定義し、複合型（Array、JSON、Tuple）を使用してデータを読み込むことができます。

SELECT *
FROM file('list-nested.json', JSONEachRow, 'page Tuple(path String, title String, owner_id UInt16), month Date, hits UInt32')
LIMIT 1

┌─page───────────────────────────────────────────────┬──────month─┬─hits─┐
│ ('Akiba_Hebrew_Academy','Akiba Hebrew Academy',12) │ 2017-08-01 │  241 │
└────────────────────────────────────────────────────┴────────────┴──────┘

ネストされた JSON オブジェクトへのアクセス

ネストされた JSON キーには、次の設定オプションを有効にすることでアクセスできます。

SET input_format_import_nested_json = 1

これにより、ネストされた JSON オブジェクトのキーをドット記法で参照できます（そのまま利用するには、これらのキーをバッククォート記号で囲むことを忘れないでください）:

SELECT *
FROM file('list-nested.json', JSONEachRow, '`page.owner_id` UInt32, `page.title` String, month Date, hits UInt32')
LIMIT 1

┌─page.owner_id─┬─page.title───────────┬──────month─┬─hits─┐
│            12 │ Akiba Hebrew Academy │ 2017-08-01 │  241 │
└───────────────┴──────────────────────┴────────────┴──────┘

この方法により、ネストされた JSON オブジェクトをフラット化したり、ネスト内の一部の値を取り出して個別のカラムとして保存したりできます。

未知のカラムをスキップする

デフォルトでは、ClickHouse は JSON データをインポートする際に、未知のカラムをスキップします。month カラムなしで元のファイルをテーブルにインポートしてみましょう。

CREATE TABLE shorttable
(
    `path` String,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY path

3 列を持つ元の JSON データを、このテーブルにそのまま挿入できます。

INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
SELECT * FROM shorttable

┌─path──────────────────────┬─hits─┐
│ 1971-72_Utah_Stars_season │    1 │
│ Aegithina_tiphia          │   34 │
│ Akiba_Hebrew_Academy      │  241 │
└───────────────────────────┴──────┘

ClickHouse はインポート時に不明なカラムを無視します。この動作は input_format_skip_unknown_fields 設定オプションで無効化できます。

SET input_format_skip_unknown_fields = 0;
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;

Ok.
クライアント側の例外:
Code: 117. DB::Exception: JSONEachRow形式の解析中に不明なフィールドが検出されました: month: (ファイル/URI: /data/clickhouse/user_files/list.json): (1行目)

ClickHouse は、JSON とテーブル列の構造が一致しない場合、例外をスローします。

BSON

ClickHouse は、BSON 形式でエンコードされたファイルへのエクスポートおよびそこからのインポートをサポートしています。この形式は、MongoDB データベースなど、いくつかの DBMS で使用されています。

BSON データをインポートするには、BSONEachRow 形式を使用します。この BSON ファイルからデータをインポートしてみましょう：

SELECT * FROM file('data.bson', BSONEachRow)

┌─path──────────────────────┬─month─┬─hits─┐
│ Bob_Dolman                │ 17106 │  245 │
│ 1-krona                   │ 17167 │    4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 17167 │    3 │
└───────────────────────────┴───────┴──────┘

同じフォーマットで BSON ファイルにエクスポートすることもできます。

SELECT *
FROM sometable
INTO OUTFILE 'out.bson'
FORMAT BSONEachRow

その後、データは out.bson ファイルにエクスポートされます。

JSON をオブジェクトとして読み込む​

JSON オブジェクトの配列​

JSON オブジェクトキー​

親オブジェクトのキー値を指定する​

JSON 配列​

JSON 配列から個々のカラムをインポートする​

JSON オブジェクトをパースせずに保存する​

ネストされたオブジェクト用のスキーマ​

ネストされた JSON オブジェクトへのアクセス​

未知のカラムをスキップする​

BSON​