PostgreSQL SELECT API Reference

`JSONB`

4 methods

二进制 JSON 类型的函数和操作符。

SELECT col, agg() OVER (PARTITION BY ... ORDER BY ...) FROM tbl

返回将 target 中给定路径的字段设置为 new_value 的结果，可选创建。

Parameters

Name	Type	Description
PARTITION BY	column-list	Divides rows into partitions for the window.
ORDER BY	column-list	Ordering inside each partition.

Returns

ResultSet

Example

postgresql

SELECT user_id, created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM events;

WITH name AS (SELECT ...) SELECT ... FROM name

从交替的键/值参数构建 JSON 对象。

Parameters

Name	Type	Description
name	identifier	CTE name.

Returns

ResultSet

Example

postgresql

WITH active AS (
  SELECT id FROM users WHERE active
)
SELECT o.* FROM orders o JOIN active a ON a.id = o.user_id;

SELECT ... FROM a, LATERAL (SELECT ... WHERE a.id = ref) b

如果左侧 JSON 值包含右侧值（顶层包含），则返回 true。

Parameters

Name	Type	Description
LATERAL	keyword	Enables cross-references to earlier FROM items.

Returns

ResultSet

Example

postgresql

SELECT u.id, latest.id AS last_order
FROM users u
LEFT JOIN LATERAL (
  SELECT id FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
) latest ON true;

INSERT|UPDATE|DELETE ... RETURNING cols

如果 JSON 对象有给定顶层键，或数组包含该字符串，则返回 true。

Parameters

Name	Type	Description
cols	column-list	Columns to return; * returns all.

Returns

ResultSet

Example

postgresql

INSERT INTO users (email) VALUES ('[email protected]')
RETURNING id, created_at;

All references Next: JSONB