postgresql 使用JSONb运算符

2024-04-13 11:54:04 125

示例

创建数据库和表

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;

输出：

->与->>

该->运算符返回原始JSON类型（可能是一个对象），而->>返回文本。

返回嵌套对象

您可以使用->返回一个嵌套对象，从而链接运算符：

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进行比较'"DharmaBums"'

或者我们可以使用->>和比较'DharmaBums'

输出：

嵌套过滤

根据嵌套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聚合函数

使用JSON运算符，结合传统的PostgreSQL聚合函数，我们可以提取所需的任何内容。您可以随意使用RDBMS。

让我们看看浏览器的用法：

 SELECTbrowser->>'name'ASbrowser,
  count(browser)
  FROMevents
  GROUPBYbrowser->>'name';

输出：

每位访客的总收入：

 SELECTvisitor_id,SUM(CAST(properties->>'amount'ASinteger))AStotal
 FROMevents
 WHERECAST(properties->>'amount'ASinteger)>0
 GROUPBYvisitor_id;

输出：

平均屏幕分辨率

 SELECTAVG(CAST(browser->'resolution'->>'x'ASinteger))ASwidth,
  AVG(CAST(browser->'resolution'->>'y'ASinteger))ASheight
 FROMevents;

输出：

此处和此处有更多示例和文档。