PostgreSQL中解析JSON：提取person_name并遍历jobs获取job_title

嘿，我来帮你搞定PostgreSQL里解析JSON提取字段的需求！先结合常见的JSON结构示例，一步步给你讲清楚怎么实现，还有封装成函数的方法～

首先假设你的JSON数据结构大概是这样的（如果实际结构略有不同，调整字段名即可）：

{
  "person_name": "John Doe",
  "jobs": [
    {"job_title": "Software Engineer"},
    {"job_title": "DevOps Specialist"}
  ]
}

1. 先明确JSON数据类型 #

PostgreSQL支持json和jsonb两种类型，jsonb是二进制存储的格式，查询和操作效率更高，优先推荐使用jsonb。

2. 提取顶层的person_name字段 #

提取顶层字段非常简单，用->>操作符可以直接返回文本类型的字段值（如果用->会返回JSON对象，需要进一步处理）。假设你的JSON数据存在某个表的data列里，提取person_name的基础写法是：

SELECT data->>'person_name' AS person_name FROM your_table;

3. 遍历jobs数组提取job_title #

要处理数组，得用jsonb_array_elements()（对应jsonb类型）或者json_array_elements()（对应json类型）来"展开"数组里的每个元素，然后再从每个元素中提取job_title。

基础查询示例 #

如果只是做查询，这样就能得到每个人的名字和对应的所有职位：

SELECT
  data->>'person_name' AS person_name,
  job->>'job_title' AS job_title
FROM your_table,
     jsonb_array_elements(data->'jobs') AS job;

封装成函数的完整示例 #

如果要把这个逻辑封装成PostgreSQL函数，比如返回包含person_name和job_title的记录集，可以这么写：

CREATE OR REPLACE FUNCTION extract_person_jobs()
RETURNS TABLE(person_name TEXT, job_title TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT
    data->>'person_name' AS person_name,
    job->>'job_title' AS job_title
  FROM your_table,
       jsonb_array_elements(data->'jobs') AS job;
END;
$$ LANGUAGE plpgsql;

调用函数直接用：

SELECT * FROM extract_person_jobs();

4. 处理空值/异常情况 #

如果你的JSON里可能存在jobs数组为空、甚至不存在的情况，可以用LEFT JOIN LATERAL搭配COALESCE来避免丢失没有职位的人员记录：

SELECT
  data->>'person_name' AS person_name,
  COALESCE(job->>'job_title', 'No job recorded') AS job_title
FROM your_table
LEFT JOIN LATERAL jsonb_array_elements(COALESCE(data->'jobs', '[]'::jsonb)) AS job ON true;

这样即使没有jobs数据，也会显示默认的"No job recorded"，不会漏掉任何人员记录。

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