PostgreSQL中如何访问JSON字段嵌套对象并做数值校验

一、直接访问指定嵌套对象（如progress1、progress2） #

PostgreSQL提供两种核心操作符来访问JSON字段的嵌套内容：

• ->：返回JSON类型的子对象/属性
• ->>：返回文本类型的属性值，方便转换为数值

要访问progress字段里progress1对象的两个属性，写法如下：

-- 获取progress1的mustWin数值
(progress->'progress1'->>'mustWin')::int

-- 获取progress1的progress数值
(progress->'progress1'->>'progress')::int

如果你的JSON字段是jsonb类型（推荐使用，性能更优），写法完全一致。

二、遍历所有嵌套的progressN对象（批量校验） #

如果存在多个类似progress1、progress2的嵌套对象，不想逐个写判断，可以用json_each函数把JSON对象展开成键值对，再批量校验：

SELECT 
    id,
    json_key AS progress_key,
    (json_value->>'mustWin')::int AS must_win,
    (json_value->>'progress')::int AS current_progress
FROM profile,
     json_each(progress) AS j(json_key, json_value)
WHERE (json_value->>'mustWin')::int = (json_value->>'progress')::int;

这个查询会列出所有满足mustWin和progress相等的用户记录及对应的progress键名。

三、结合校验执行积分增加操作 #

直接编写UPDATE语句，针对单个或多个progress对象做校验后更新积分：

1. 针对单个progress1的情况：

UPDATE profile
SET score = score + 10 -- 假设每次满足条件加10积分
WHERE (progress->'progress1'->>'mustWin')::int = (progress->'progress1'->>'progress')::int
-- 可额外添加防重复奖励的条件
AND (progress->'progress1'->>'reward_given')::bool IS NOT TRUE;

2. 批量处理所有满足条件的progress对象：
   如果要给每个满足条件的progress对象都加积分，可以用CTE先筛选符合条件的记录，再执行更新：

WITH eligible_profiles AS (
    SELECT DISTINCT id
    FROM profile,
         json_each(progress) AS j(json_key, json_value)
    WHERE (json_value->>'mustWin')::int = (json_value->>'progress')::int
      AND (json_value->>'reward_given')::bool IS NOT TRUE
)
UPDATE profile
SET score = score + 10
WHERE id IN (SELECT id FROM eligible_profiles);

也可以在更新积分的同时，给对应的progress对象标记奖励已发放，避免重复操作：

UPDATE profile
SET 
    score = score + 10,
    progress = jsonb_set(progress, ARRAY[json_key, 'reward_given'], 'true'::jsonb)
FROM (
    SELECT id, json_key
    FROM profile,
         json_each(progress) AS j(json_key, json_value)
    WHERE (json_value->>'mustWin')::int = (json_value->>'progress')::int
      AND (json_value->>'reward_given')::bool IS NOT TRUE
) AS eligible
WHERE profile.id = eligible.id;

内容的提问来源于stack exchange，提问作者user10596155