使用 JSONb 運算子

建立資料庫和表

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
);

填充資料庫

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;

輸出：

-> vs ->>

-> 運算子返回原始 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"';

注意 WHERE 使用 -> 所以我們必須比較 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
);

我們將在此表中儲存事件，例如網頁瀏覽。每個事件都有屬性，可以是任何內容（例如當前頁面），也可以傳送有關瀏覽器的資訊（如作業系統，螢幕解析度等）。這兩種都是完全自由的形式，可能會隨著時間的推移而改變（因為我們想到要跟蹤的額外內容）。

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 聚合函式

使用 JSON 運算子，結合傳統的 PostgreSQL 聚合函式，我們可以提取任何我們想要的東西。你可以隨心所欲地使用 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;
  

輸出：

更多示例和文件在這裡和這裡 。