postgresql
JSONのサポート

前書き

純粋なJSONテーブルを作成する

純粋なJSONテーブルを作成するには、 JSONB型の単一のフィールドを指定する必要があります。

CREATE TABLE mytable (data JSONB NOT NULL);

基本インデックスも作成する必要があります。

CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);

この時点で、テーブルにデータを挿入して効率的にクエリを実行できます。

複雑なJSONドキュメントのクエリ

テーブルで複雑なJSONドキュメントを作成する：

CREATE TABLE mytable (data JSONB NOT NULL);
CREATE INDEX mytable_idx ON mytable USING gin (data jsonb_path_ops);
INSERT INTO mytable VALUES($$
{
    "name": "Alice",
    "emails": [
        "[email protected]",
        "[email protected]"
    ],
    "events": [
        {
            "type": "birthday",
            "date": "1970-01-01"
        },
        {
            "type": "anniversary",
            "date": "2001-05-05"
        }
    ],
    "locations": {
        "home": {
            "city": "London",
            "country": "United Kingdom"
        },
        "work": {
            "city": "Edinburgh",
            "country": "United Kingdom"
        }
    }
}
$$);

トップレベル要素のクエリ：

SELECT data->>'name' FROM mytable WHERE data @> '{"name":"Alice"}';

配列内の単純な項目を照会する：

SELECT data->>'name' FROM mytable WHERE data @> '{"emails":["[email protected]"]}';

配列内のオブジェクトを照会する：

SELECT data->>'name' FROM mytable WHERE data @> '{"events":[{"type":"anniversary"}]}';

ネストされたオブジェクトのクエリ：

SELECT data->>'name' FROM mytable WHERE data @> '{"locations":{"home":{"city":"London"}}}';

@>パフォーマンスは->と->> ->と比較して

クエリのWHERE部分で@> 、 ->および->>を使用する場合のパフォーマンスの違いを理解することが重要です。これらの2つのクエリはほぼ同じですが、

SELECT data FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data FROM mytable WHERE data->'name' = '"Alice"';
SELECT data FROM mytable WHERE data->>'name' = 'Alice';

最初のステートメントは上で作成したインデックスを使用しますが、後者のステートメントは完全なテーブルスキャンを必要としません。

結果のデータを取得するときに->演算子を使用することは可能ですので、次のクエリもインデックスを使用します：

SELECT data->'locations'->'work' FROM mytable WHERE data @> '{"name":"Alice"}';
SELECT data->'locations'->'work'->>'city' FROM mytable WHERE data @> '{"name":"Alice"}';

JSONb演算子の使用

DBとテーブルの作成

DROP DATABASE IF EXISTS books_db;
CREATE DATABASE books_db WITH ENCODING='UTF8' TEMPLATE template0;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

DBへのデータ入力

INSERT INTO books(client, data) values (
    'Joe', 
    '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'
),(
    'Jenny', 
    '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'
),(
    'Jenny', 
    '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }'
);

テーブルブックの中のすべてを見てみましょう：

SELECT * FROM books;

出力：

->演算子はJSON列から値を返します

1列を選択する：

SELECT client, 
    data->'title' AS title
    FROM books;

出力：

2列の選択：

SELECT client, 
   data->'title' AS title, data->'author' AS author
   FROM books;

出力：

->対->>

->演算子は元のJSON型（オブジェクトでもよい）を返しますが、 ->>はテキストを返します。

NESTEDオブジェクトを返す

->を使用すると、ネストしたオブジェクトを返すことができ、演算子を連鎖できます。

SELECT client, 
   data->'author'->'last_name' AS author
   FROM books;

出力：

フィルタリング

JSON内の値に基づいて行を選択します。

 SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'title' = '"Dharma Bums"';

JSON '"Dharma Bums"'と比較する必要があるので、どこで->使用しているかを確認してください

あるいは、 ->>を使って'Dharma Bums'と比較することもできます

出力：

ネストされたフィルタリング

入れ子になったJSONオブジェクトの値に基づいて行を検索する：

SELECT 
 client,
 data->'title' AS title
 FROM books
  WHERE data->'author'->>'last_name' = 'Kerouac';

出力：

現実世界の例

CREATE TABLE events (
  name varchar(200),
  visitor_id varchar(200),
  properties json,
  browser json
);

この表には、ページビューなどのイベントを保存します。各イベントにはプロパティ（現在のページなど）があり、ブラウザに関する情報（OS、画面解像度など）も送信されます。これらは両方とも完全にフリーフォームであり、時間の経過と共に変化する可能性があります（追跡する余分なものを考えると）。

INSERT INTO events (name, visitor_id, properties, browser) VALUES
(
  'pageview', '1',
  '{ "page": "/" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'pageview', '2',
  '{ "page": "/" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1920, "y": 1200 } }'
),(
  'pageview', '1',
  '{ "page": "/account" }',
  '{ "name": "Chrome", "os": "Mac", "resolution": { "x": 1440, "y": 900 } }'
),(
  'purchase', '5',
  '{ "amount": 10 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1024, "y": 768 } }'
),(
  'purchase', '15',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),(
  'purchase', '15',
  '{ "amount": 500 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
);

今すぐすべてを選択してください：

SELECT * FROM events;

出力：

JSON演算子+ PostgreSQL集約関数

従来のPostgreSQL集約関数と組み合わされたJSON演算子を使用して、必要なものを引き出すことができます。あなたは自由にRDBMSを手に入れることができます。

• ブラウザの使用状況を見てみましょう：

    SELECT browser->>'name' AS browser, 
      count(browser)
      FROM events
      GROUP BY browser->>'name';
  

出力：

• ビジターあたりの総収入：

    SELECT visitor_id, SUM(CAST(properties->>'amount' AS integer)) AS total
    FROM events
    WHERE CAST(properties->>'amount' AS integer) > 0
    GROUP BY visitor_id;
  

出力：

• 平均画面解像度

    SELECT AVG(CAST(browser->'resolution'->>'x' AS integer)) AS width,
      AVG(CAST(browser->'resolution'->>'y' AS integer)) AS height
    FROM events;
  

出力：

より多くの例とドキュメントはこことここにあります 。