如何解析PostgreSQL表存储的JSON结构中多个name参数的对应值

你当前的查询固定指定了数组索引0，因此只能获取inputs数组中的第一个对象。要批量提取数组内所有对象的字段，需要使用PostgreSQL的jsonb_array_elements函数展开JSON数组，配合横向连接实现每行对应一个数组元素的效果。

实现代码 #

SELECT
    s.projectid,
    s.prompttype,
    input_obj->>'desc' AS desc,
    input_obj->>'name' AS name,
    input_obj->>'values' AS values,
    s.created,
    s.modified
FROM source_redshift.staticprompts AS s
CROSS JOIN LATERAL jsonb_array_elements(s.inputs::jsonb -> 'inputs') AS t(input_obj);

代码说明 #

• jsonb_array_elements(s.inputs::jsonb -> 'inputs')：将inputs字段下的JSON数组展开为行集合，每一行对应数组内的一个对象，别名为input_obj
• CROSS JOIN LATERAL：对原表的每一行，都关联展开后的所有数组元素行，不会丢失原表中inputs数组为空的记录，如果需要过滤掉空数组的行可以换成INNER JOIN LATERAL
• input_obj->>'key'：直接从展开后的单个JSON对象中提取对应字段的文本值，语法比路径写法更简洁

如果你的inputs字段存储的是json类型而非jsonb，把jsonb_array_elements替换为json_array_elements即可。

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