如何通过PostgreSQL视图从JSONB列直接生成类DataFrame结果？

假设你的表包含id列和存储JSON数据的JSONB类型列（比如命名为json_data），可以通过以下SQL创建视图得到目标结构化结果：

场景1：JSONB列存储单个对象 #

如果每行的JSONB列直接是{"myyear":2016,"value":5}这类单个对象，直接提取字段即可：

CREATE VIEW target_view AS
SELECT
    id,
    (json_data ->> 'myyear')::INTEGER AS myyear,
    (json_data ->> 'value')::INTEGER AS value
FROM your_table_name;

场景2：JSONB列存储对象数组 #

如果JSONB列是[{"myyear":2016,"value":5}]这类对象数组，需要先展开数组再提取：

CREATE VIEW target_view AS
SELECT
    t.id,
    (item ->> 'myyear')::INTEGER AS myyear,
    (item ->> 'value')::INTEGER AS value
FROM your_table_name t
CROSS JOIN LATERAL jsonb_array_elements(t.json_data) AS item;

关键说明 #

• ->> 用于提取JSONB中指定键的文本值，::INTEGER将其转换为整数类型（可根据实际数据类型调整）
• jsonb_array_elements 配合LATERAL实现JSON数组的行展开，把数组中的每个对象转为单独的行

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