记录下Postgres的JSON相关操作

准备表结构和数据

表结构

1
2
3
4
CREATE TABLE orders (
ID serial NOT NULL PRIMARY KEY,
info json NOT NULL
);

数据

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO orders (info)
VALUES
(
'{ "customer": "Lily Bush", "items": [{"product": "Diaper","qty": 24}, {"product": "PC","qty": 300}]}'
),
(
'{ "customer": "Josh William", "items": [{"product": "Toy Car","qty": 1}, {"product": "NoteBook","qty": 2}]}'
),
(
'{ "customer": "Mary Clark", "items": [{"product": "Toy Train","qty": 2}]}'
);

查询JSON数据

操作符
Operator Right Operand Type Description Example Example Result
-> int Get JSON array element (indexed from zero, negative integers count from the end) '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text Get JSON object field by key '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int Get JSON array element as text '[1,2,3]'::json->>2 3
->> text Get JSON object field as text '{"a":1,"b":2}'::json->>'b' 2
#> text[] Get JSON object at specified path '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"}
#>> text[] Get JSON object at specified path as text '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

使用运算符->获取JSON对象的字段

1
2
3
4
SELECT
info -> 'customer' AS customer
FROM
orders;

使用->>获取JSON对象的文本字段

1
2
3
4
SELECT
info ->> 'customer' AS customer
FROM
orders;

因为-> operator返回一个JSON对象,可以链式操作检索特定的节点。例如,以下语句返回所有售出的产品:

1
2
3
4
5
6
SELECT
info -> 'items' -> 0 ->> 'product' as product
FROM
orders
ORDER BY
product;

第一个info -> 'item' 返回一个JSON对象,info -> 'items' -> 0返回items数组中第一个元素的JSON对象,最后info -> 'items' -> 0 ->> 'product'以文本的形式返回所有的product

WHERE语句中使用操作符
1
2
3
4
5
6
SELECT
info ->> 'customer' AS customer
FROM
orders
WHERE
info -> 'items' -> 0 ->> 'product' = 'Diaper'

聚合函数在JSON中的应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT
MIN (
CAST (
info -> 'items' ->0 ->> 'qty' AS INTEGER
)
),
MAX (
CAST (
info -> 'items' ->0 ->> 'qty' AS INTEGER
)
),
SUM (
CAST (
info -> 'items' ->0 ->> 'qty' AS INTEGER
)
),
AVG (
CAST (
info -> 'items' ->0 ->> 'qty' AS INTEGER
)
)

FROM
orders

JSON处理函数

json_each函数

json_each()将最外面的JSON对象扩展成一组键/值对。

1
2
3
4
SELECT
json_each (info)
FROM
orders;

或者对于单条记录

1
2
3
4
SELECT
*
FROM
json_each((select info from orders where id = 1));

json_each_textjson_each效果相同,返回的是文本类型

json_object_key函数

json_object_key()返回最外面的JSON对象中的一组键。

1
2
3
4
SELECT
json_object_keys (info->'items' ->0)
FROM
orders;

更多内容参照PostgreSQL官方文档