なぜ VARCHAR(n) ではなく TEXT なのですか。

PostgreSQL がどちらも同じように格納し、TEXT には推測すべき長さが無いからです。`VARCHAR(n)` は制約を足します。その上限があなたの業務の規則なら役に立ち、標本の中でいちばん長い値にすぎないなら罠になります。規則が分かってから足してください。格納のしかたは変わりません。

なぜ識別子が二重引用符で囲まれているのですか。

引用符が名前を厳密にするからです。囲まなければ、PostgreSQL は識別子を小文字に畳みます。それでも列は動きますが、解析器が許さない文字を鍵が求めた時点で止まります。すべて囲めば規則は二つでなく一つで済み、`"order"` や `"group"` も予約語ではなく普通の列になります。

JSON と JSONB のどちらですか。

JSONB です。索引を張り、効率よく問い合わせられるのはこちらだからです。素の `json` は鍵の順序や空白も含めて元の文字をそのまま保ちます。これが効くのは、文書をバイト単位で返すつもりのときだけです。その場合は型を変えてください。表のほかの部分は何も依存していません。

JSONをPostgreSQLに変換｜psqlにそのまま貼れる形に

JSONのサンプルをPostgreSQLのCREATE TABLEに変換します。文字列は TEXT、入れ子は JSONB になり、行によって欠けるキーは nullable として扱われます。

このツールの動作

JSON の標本が CREATE TABLE になります。

{ "id": 1, "firstName": "Ada", "score": 9.5, "address": { "city": "Paris" } }

CREATE TABLE "root" (
  "id" BIGINT NOT NULL,
  "first_name" TEXT NOT NULL,
  "score" DOUBLE PRECISION NOT NULL,
  "address" JSONB NOT NULL
);

名前は snake_case で、引用符で囲みます。名前は厳密に保たれ、"order" のような予約語も気兼ねなく列になります。

TEXT には作り出す長さが無い

PostgreSQL は TEXT と VARCHAR(n) を同じように格納します。違うのは制約だけです。標本が言えるのは、たまたま含んでいた最長の値であって、本当の上限ではありません。だから上限は書きません。あとから VARCHAR(40) を足すのはあなたの業務についての判断であり、格納の費用は変わりません。

MySQL の頁にある VARCHAR(255) の注意書きが、この頁に無いのもそのためです。ここには間違えるべき長さがありません。

すべての行を、最初の一つだけでなく

[{ "a": 1 }, { "b": "x" }]

CREATE TABLE "root_item" (
  "a" BIGINT NULL,
  "b" TEXT NULL
);

以前、表は最初のオブジェクトだけから作られていました。あなた自身の標本の二行目には、行き場がありませんでした。今は列がすべてのオブジェクトの和になり、一部の行に無い鍵は null 可になります。実際にそうだからです。

型も同じように統合されます。同じキーが、一方のオブジェクトでは数値、もう一方では文字列という場合、どのスカラー型の列も両方は受け取れないため、列は JSONB になります。整数と小数が並ぶ場合は話が別で、DOUBLE PRECISION が両方を覆います。

SQL に列が無いものは JSONB へ

入れ子のオブジェクトと配列は JSONB になります。関連表も外部キーもありません。標本が示すのは形であって関係ではなく、結合を作り出せば、表と鍵と向きを一度に当てにいくことになります。

JSONB は問い合わせられる形です。->、->>、@>、必要なら GIN 索引も使えます。素の json は元の文字を保ちますが、それが効くのは文書をそのまま返すつもりのときだけです。

行を実際に入れる

PostgreSQL に bq load にあたるものはありません。COPY が前提にしているのは一行一件のテキストと CSV であって、JSONオブジェクトの配列ではありません。上のテーブルは、仕事の半分です。

素直な道は jsonb_populate_recordset です。JSONのキーと列名を突き合わせますが、その照合は一字一句そのままです。そこに落とし穴があります。上のテーブルは firstName を first_name に改名しているので、この道を通ると、その列は何も言われないまま NULL になります。

上のテーブルに合う対応づけは明示的に書きます。どのキーがどの列を埋めるのかが、そのまま読めます。

INSERT INTO "root" ("id", "first_name", "score", "address")
SELECT (e->>'id')::bigint,
       e->>'firstName',
       (e->>'score')::double precision,
       e->'address'
FROM jsonb_array_elements(:'doc'::jsonb) AS e;

:'doc' は文書を持つ psql の変数です。ファイルが引数に収まるうちは psql -v doc="$(cat data.json)" -f load.sql で足ります。それを超えたら、いったん一列だけの中継テーブルに本文を写し、そこから選び直してください。問い合わせの形は変わりません。

標本が言えないこと

主キーはありません。 ある文書で一意に見える id は、次の文書について何も約束しません。
索引も既定値も CHECK もありません。 どれもデータについての主張であって、読み取りではありません。
値があったところは NOT NULL、null と、一部のオブジェクトに無い鍵は NULL です。

プライバシー

すべての処理はブラウザ内のJavaScriptだけで完結します。データがアップロードされることはないため、機密情報でも安心して利用でき、オフラインでも動作します。

JSONをPostgreSQLに変換｜psqlにそのまま貼れる形に

JSON 入力

PostgreSQL 出力