PostgreSQL：json数据处理

1. 简介：

• 在 PostgreSQL 中，处理 JSON 数据的功能非常强大，尤其是针对嵌套的 JSON 结构和 JSON 数组的查询。以下是一些处理 JSON 数据的常见操作和具体的案例，主要展示如何从 JSON 和 JSON 数组中获取指定 key 的值。

2. JSON 处理关键函数

• ->：获取 JSON 对象中的某个 key 对应的值，结果类型为 JSON。
• ->>：获取 JSON 对象中的某个 key 对应的值，结果类型为文本。
• jsonb_array_elements()：将 JSON 数组中的每个元素展开为独立的行。
• jsonb_extract_path_text()：从嵌套的 JSON 对象中提取某个路径的文本值。

3. 处理普通 JSON 对象

• 使用->、->> 处理

-- json
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::json)->'price' AS price;
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::json)->>'price' AS price;
-- jsonb
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::jsonb)->'price' AS price;
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::jsonb)->>'price' AS price;

• 结果

 - 100 
 - 100 
 - 100 
 - 100

4. 处理多层嵌套的 JSON 对象

• 使用->、->> 处理

-- json
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::json)->'tags'->'hot' AS price;
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::json)->'tags'->>'hot' AS price;
-- jsonb
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::jsonb)->'tags'->'hot' AS price;
SELECT  ('{"price": 100, "color": "red", "tags": {"hot": true, "discount": false}}'::jsonb)->'tags'->>'hot' AS price;

• 结果

 - true
 - true
 - true
 - true

5. 处理 JSON 数组数据

a. 将Json数组拆分为多条数据：

• 实现：jsonb_array_elements

SELECT  jsonb_array_elements('[{"size": "S", "stock": 100}, {"size": "M", "stock": 200}]') as line

• 结果

line                       |
---------------------------+
{"size": "S", "stock": 100}|
{"size": "M", "stock": 200}|

• 案例：

现在假设我们有一个包含 JSON 数组的表，表中的字段 specs 存储了产品规格的 JSON 数组。

CREATE TABLE product_specs (
    id SERIAL PRIMARY KEY,
    name TEXT,
    specs JSONB
);

插入数据：

INSERT INTO product_specs (name, specs)
VALUES 
    ('Product A', '[{"size": "S", "stock": 100}, {"size": "M", "stock": 200}]'),
    ('Product B', '[{"size": "L", "stock": 50}, {"size": "XL", "stock": 30}]');

如果我们想要从 specs 数组中提取每个产品的 size 和 stock，可以使用 jsonb_array_elements() 函数来展开数组：

SELECT 
    name, 
    spec->>'size' AS size, 
    spec->>'stock' AS stock
FROM 
    product_specs, 
    jsonb_array_elements(specs) AS spec;

-- 或
SELECT 
    name, 
    spec->>'size' AS size, 
    spec->>'stock' AS stock
FROM 
    product_specs
CROSS JOIN 
    jsonb_array_elements(specs) AS spec

结果：

name     |size|stock|
---------+----+-----+
Product A|S   |100  |
Product A|M   |200  |
Product B|L   |50   |
Product B|XL  |30   |

b. 条件查询 JSON 数组中的数据

针对a中案例表数据实现：

SELECT 
    name, 
    spec->>'size' AS size, 
    spec->>'stock' AS stock
FROM 
    product_specs, 
    jsonb_array_elements(specs) AS spec
WHERE 
    (spec->>'stock')::INTEGER > 100;

结果：

name     |size|stock|
---------+----+-----+
Product A|M   |200  |

6. 待续…