将JSON函数与PostgreSQL子查询结合使用

-- 假设有一个名为"users"的表，其中包含一个名为"info"的JSON字段

-- 查询所有用户的姓名和年龄
SELECT info->>'name' AS name, info->>'age' AS age
FROM users;

-- 查询年龄大于30岁的用户的姓名和年龄
SELECT info->>'name' AS name, info->>'age' AS age
FROM users
WHERE (info->>'age')::int > 30;

-- 查询拥有特定技能的用户的姓名和技能列表
SELECT info->>'name' AS name, info->'skills' AS skills
FROM users
WHERE info->'skills' @> '[{"name": "programming"}]';

-- 使用子查询获取用户的姓名和技能列表，并按技能数量排序
SELECT name, skills
FROM (
  SELECT info->>'name' AS name, info->'skills' AS skills, json_array_length(info->'skills') AS skill_count
  FROM users
) AS subquery
ORDER BY skill_count DESC;