使用JSONB函数提取数组中fullname字段报错求助

问题背景 #

存在一张名为temporary_data的表，表内包含同名的temporary_data字段，该字段存储的JSON结构如下：

{
 "FormPayment": {
        "student": [
            {
                "fullname": "name student1 ",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "student",
                "willPay": true
            },
            {
                "fullname": "name student2",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "student",
                "willPay": true
            },
            {
                "fullname": "name student3",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "student",
                "willPay": true
            }
        ],
        "advisor": [
            {
                "fullname": "name advisor",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "advisor",
                "isParticipant": "yes",
                "willPay": true
            }
        ],
        "coadvisors": [
            {
                "fullname": "name coadvisors 1",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "coadvisor",
                "isParticipant": "yes",
                "willPay": true
            },
            {
                "fullname": "name coadvisors 2",
                "rate": 210,
                "meal": 7,
                "mealValue": 175,
                "finalValue": 385,
                "role": "coadvisor",
                "isParticipant": "no",
                "willPay": false
            }
        ]
    }
}

需要提取该JSON中所有fullname字段值，尝试的SQL语句及报错如下：

尝试的SQL：

SELECT elements->>'fullname' as fullname
FROM (
    SELECT jsonb_array_elements(temporary_data->'FormPayment'->'student') as elements
    FROM temporary_data
) subquery;

报错信息：

ERROR:  function jsonb_array_elements(json) does not exist
LINE 31:     SELECT jsonb_array_elements(temporary_data->'FormPayment...
                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 687

错误核心原因 #

报错本质是数据类型不匹配：你的temporary_data字段是JSON类型，而jsonb_array_elements函数要求参数必须是JSONB类型，直接调用会因类型不兼容触发错误。

解决方案 #

方案1：显式转换为JSONB类型后处理 #

通过::jsonb将原JSON字段转换为JSONB类型，再使用jsonb_array_elements函数：

-- 仅提取student数组中的fullname
SELECT elements->>'fullname' as fullname
FROM (
    SELECT jsonb_array_elements(temporary_data::jsonb->'FormPayment'->'student') as elements
    FROM temporary_data
) subquery;

方案2：使用JSON类型对应的函数 #

如果不想转换字段类型，直接使用针对JSON类型的json_array_elements函数：

-- 仅提取student数组中的fullname
SELECT elements->>'fullname' as fullname
FROM (
    SELECT json_array_elements(temporary_data->'FormPayment'->'student') as elements
    FROM temporary_data
) subquery;

方案3：一次性提取所有数组中的fullname #

要同时提取student、advisor、coadvisors三个数组里的所有fullname，使用UNION ALL（保留重复值，比UNION效率更高）：

SELECT elements->>'fullname' as fullname
FROM (
    -- 提取student的fullname
    SELECT json_array_elements(temporary_data->'FormPayment'->'student') as elements
    FROM temporary_data
    UNION ALL
    -- 提取advisor的fullname
    SELECT json_array_elements(temporary_data->'FormPayment'->'advisor') as elements
    FROM temporary_data
    UNION ALL
    -- 提取coadvisors的fullname
    SELECT json_array_elements(temporary_data->'FormPayment'->'coadvisors') as elements
    FROM temporary_data
) subquery;

方案4：更简洁的LATERAL JOIN写法 #

使用LATERAL JOIN简化语句，避免嵌套子查询：

SELECT elem->>'fullname' AS fullname
FROM temporary_data,
     json_array_elements(temporary_data->'FormPayment'->'student') elem
UNION ALL
SELECT elem->>'fullname' AS fullname
FROM temporary_data,
     json_array_elements(temporary_data->'FormPayment'->'advisor') elem
UNION ALL
SELECT elem->>'fullname' AS fullname
FROM temporary_data,
     json_array_elements(temporary_data->'FormPayment'->'coadvisors') elem;

内容的提问来源于stack exchange，提问作者Rudinei Pereira Dias