Sql

JSONファイルをPostgreSQLにインポートするにはどうすればよいですか？



How Can I Import Json File Into Postgresql



解決：

コマンドラインpsqlツールを使用してpostgresデータベースのJSON列に複数行のJSONオブジェクトをインポートする簡単な方法があり、JSONをSQLステートメントに明示的に埋め込む必要はありません。この手法はpostgresqlのドキュメントに記載されていますが、少し隠されています。

秘訣は、バッククォートを使用してJSONをpsql変数にロードすることです。たとえば、複数行のJSONファイルが /tmp/test.json そのような：



{'犬'： '猫'、 'カエル'： '友愛'}
次のSQLを使用して、一時テーブルにロードできます。
 
sql>  set content `cat / tmp / test.json` sql> create temp table t（j jsonb）; sql> t値に挿​​入（： 'content'）; sql> select * from t;
結果が得られます：
 
 
 
j────────────────────────────────{'犬'： '猫'、 'カエル'： '友愛'} （1列）
データに対して直接操作を実行することもできます。
 
sql> select： 'content' :: jsonb-> 'dog'; ？桁？ ──────────「猫」（1列）
カバーの下でこれ は JSONをSQLに埋め込むだけですが、psqlに補間自体を実行させる方がはるかに便利です。
  
JSONをSQLステートメントにフィードして、情報を抽出し、それをテーブルに挿入できます。 JSON属性の名前がテーブルの列とまったく同じである場合は、次のようにすることができます。
 
 
 
with customer_json（doc）as（values（ '[{' id '：23635、' name '：' Jerry Green '、' comment '：' Facebookからインポートされました。 '}、{' id '：23636、' name '： 'John Wayne'、 'comment'： 'Facebookからインポートされました。'}] ':: json））顧客に挿入（id、name、comment）selectp。* from customer_json l cross joinlateral json_populate_recordset（null :: customer、 doc）競合（id）のpとして、セット名= exclude.name、コメント= excluded.commentを更新します。
新しい顧客が挿入され、既存の顧客が更新されます。 「魔法」の部分はJSONオブジェクトのリレーショナル表現を生成するjson_populate_recordset（null :: customer、doc）。
 
 
上記は、次のようなテーブル定義を前提としています。
 
テーブルcustomerを作成します（id整数主キー、名前テキストはnullではない、コメントテキスト）;
 
データがファイルとして提供されている場合は、最初にそのファイルをデータベース内のテーブルに配置する必要があります。このようなもの：
 
ログに記録されていないテーブルcustomer_import（doc json）;を作成します。
次に、ファイルをそのテーブルの1つの行にアップロードします。を使用して copyコマンドpsql（またはSQLクライアントが提供するもの）：
 
 copy customer_import from'customers.json '...。
次に、上記のステートメントを使用できます。CTEを削除して、ステージングテーブルを使用するだけです。
 
顧客に挿入（id、name、comment）selectp。* from customer_import l cross joinlateral json_populate_recordset（null :: customer、doc）as p on context（id）do update set name = exclude.name、comment = exclude.comment ; 
ビッグデータに近い場合、ファイルからjsonをインポートする最も効率的な方法は、ファイルから単一のjsonをインポートするのではなく、単一の列csvをインポートするように見えます。1行のjsonのリスト：
 
data.json.csv：
 
{'id'：23635、 'name'： 'Jerry Green'、 'comment'： 'Facebookからインポートされました。'} {'id'：23636、 'name'： 'John Wayne'、 'comment'： 'インポート元フェイスブック。'}
次に、psqlの下で：
 
テーブルt（j jsonb） copy t from'd： path  data.json.csv 'を作成します
json（行）ごとに1つのレコードがtテーブルに追加されます。
 
' copy from'インポートはcsv用に作成されたため、データを1行ずつロードします。その結果、後で分割される単一のjson配列ではなく、行ごとに1つのjsonを読み取ると、中間テーブルは使用されません。
 
その多くは、ヒットする可能性が低くなります 最大入力行サイズの制限 これは、入力jsonファイルが大きすぎる場合に発生します。
 
したがって、最初に入力を単一列のcsvに変換してから、copyコマンドを使用してインポートします。