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

输出：

更多示例和文档在这里和这里 。