使用 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;
    

輸出:

在此處輸入影象描述

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