NestJS更新PostgreSQL的jsonb[]字段失败问题求助

问题背景 #

需要更新PostgreSQL中jsonb[]类型的experience字段，通过Postman的Patch请求传入数据，pgAdmin中手动编写的SQL可正常执行更新，但整合到NestJS服务后出现报错。

Postman传入的数据结构 #

{"id": "4", "user": {"id": "01JC32FBK3DYJMXZPDAVJA3NS0", "firstName": "Mar", "lastName": "Gonzalez", "phoneNumber": "+8123456789", "experience": null, "cognitoSub": null, "avatar": "avatar.png", "group": "user", "specialization": null, "email": "maria.gonzalez@example.com", "emailConfirmed": false, "consent": true, "createdAt": "2024-11-07T10:24:44.131Z", "updatedAt": "2024-11-20T07:02:49.467Z", "deletedAt": null}, "slug": "maria-gonzalez-slug", "firstName": "Mar", "lastName": "Gonzalez", "jobTitle": "tiler", "email": "maria.gonzalez@example.com", "phoneNumber": "+8123456789", "photo": "avatar.png", "city": "Warsaw", "country": "cyprus", "positionName": "tiler", "experienceYears": "3", "drivingLicense": ["B"], "availableSince": "2_weeks", "salaryRange": "10200-11700", "currencySalary": "USD", "preferredCountries": ["spain", "france"], "contractTypes": ["b2b", "employment_contract"], "skills": [{"other": ["custom_skill"], "predefined": [{"positionName": "drywall_installer", "positionSkills": ["measuring_cutting", "installation_support_structures"]}]}], "experience": [{"id": "2aed5735-4b8d-4555-b7d9-7256950770d0", "company": "IBM Updated", "country": "spain", "endDate": "2018-05-01", "startDate": "2015-02-01", "positionName": "drywall_installer"}, {"id": "f4ad4a98-6e05-4f9b-8672-a65da6f20dcd", "company": "IBMMMM", "country": "poland", "endDate": "2020-05-01", "startDate": "2017-02-01", "positionName": "drywall_installer"}, {"id": "f9d11f00-26c2-4a30-bf4c-c2df3e904a21", "company": "IBMMMM", "country": "poland", "endDate": "2022-05-01", "startDate": "2019-01-01", "positionName": "other", "positionOther": "Construction Project Manager"}, {"id": "82e1580a-9017-4ad3-aa94-98054f9ac400", "company": "IBMMMM", "country": "Germany", "endDate": "2023-06-01", "startDate": "2021-01-01", "positionName": "devops_engineer"}], "education": [{"title": "Software Engineering", "dateTo": "2015-05-01", "dateFrom": "2013-02-01", "description": "Bachelor's degree in Software Engineering.", "institution": "Poznan University of Technology"}], "languages": ["spanish", "french"], "hobbies": ["taniec", "moda", "gotowanie"], "nativeLanguage": "polish"}

pgAdmin中可正常执行的SQL语句 #

WITH expanded AS (
    SELECT
        id,
        unnest(experience) AS elem
    FROM user_competences
    WHERE id = '4'
),
updated AS (
    SELECT
        id,
        CASE
            WHEN elem->>'id' = '2aed5735-4b8d-4555-b7d9-7256950770d0' THEN jsonb_set(elem, '{company}', '"IBM Updated"'::jsonb)
            ELSE elem
        END AS updated_elem
    FROM expanded
)
UPDATE user_competences
SET experience = (
    SELECT array_agg(updated_elem)
    FROM updated
    WHERE updated.id = user_competences.id
)
WHERE id = '4';

NestJS中的实现代码 #

async updateExperience(talentId: string, experienceUpdates: ExperienceDto[]): Promise<void> {
        for (const update of experienceUpdates) {
            const query = `
                WITH expanded AS (
                    SELECT
                        id,
                        jsonb_array_elements(experience) AS elem
                    FROM user_competences
                    WHERE id = $1
                ),
                updated AS (
                    SELECT
                        id,
                        CASE
                            WHEN elem->>'id' = $2 THEN jsonb_set(elem, '{company}', to_jsonb($3))
                            ELSE elem
                        END AS updated_elem
                    FROM expanded
                )
                UPDATE user_competences
                SET experience = (
                    SELECT array_agg(updated_elem)::jsonb[]
                    FROM updated
                    WHERE updated.id = user_competences.id
                )
                WHERE id = $1;
            `

            const parameters = [
                talentId, // $1
                update.id, // $2
                update.company, // $3
            ]

            console.log('Executing query with:', parameters)

            await this.talentRepository.query(query, parameters)
        }
    }

    async update(id: string, updateTalentDto: TalentUpdateDto): Promise<TalentResponseDto> {
        const talent = await this.talentRepository.findOne({
            where: { id },
            relations: ['user'],
        })

        if (!talent) {
            throw new NotFoundException(`Talent with ID ${id} not found`)
        }

        if (updateTalentDto.experience) {
            await this.updateExperience(id, updateTalentDto.experience)
        }

        if (talent.user) {
            talent.user.avatar = updateTalentDto.photo || talent.user.avatar
            talent.user.firstName = updateTalentDto.firstName || talent.user.firstName
            talent.user.lastName = updateTalentDto.lastName || talent.user.lastName
            talent.user.email = updateTalentDto.email || talent.user.email
            talent.user.phoneNumber = updateTalentDto.phoneNumber || talent.user.phoneNumber

            await this.usersRepository.save(talent.user)
        }

        Object.assign(talent, updateTalentDto)

        return (await this.findOneBy({ id })) as TalentResponseDto
    }

错误信息 #

{"error": "22P02", "message": "nieprawidłowy literał tablicy: \"[{\"id\":\"2aed5735-4b8d-4555-b7d9-7256950770d0\",\"company\":\"New IBM Company\"},{\"id\":\"f9d11f00-26c2-4a30-bf4c-c2df3e904a21\",\"company\":\"Senior Manager\"}]\"", "timestamp": "2024-11-20T20:08:24.271Z", "traceId": "3b7065ba-5981-4780-8fcd-48126e714dc8"}

问题分析 #

错误信息中的波兰语意为“无效的数组字面量”，核心问题：

1. 混淆了数组展开函数：jsonb_array_elements用于解析单个jsonb对象内的数组，而experience是PostgreSQL的jsonb[]数组类型，应该用unnest展开
2. 多余的类型转换：array_agg(updated_elem)返回的已是jsonb[]类型，强制转换::jsonb[]会导致格式冲突

解决方案 #

修改updateExperience方法中的SQL逻辑：

async updateExperience(talentId: string, experienceUpdates: ExperienceDto[]): Promise<void> {
    for (const update of experienceUpdates) {
        const query = `
            WITH expanded AS (
                SELECT
                    id,
                    unnest(experience) AS elem
                FROM user_competences
                WHERE id = $1
            ),
            updated AS (
                SELECT
                    id,
                    CASE
                        WHEN elem->>'id' = $2 THEN jsonb_set(elem, '{company}', to_jsonb($3))
                        ELSE elem
                    END AS updated_elem
                FROM expanded
            )
            UPDATE user_competences
            SET experience = (
                SELECT array_agg(updated_elem)
                FROM updated
                WHERE updated.id = user_competences.id
            )
            WHERE id = $1;
        `;

        const parameters = [
            talentId, // $1
            update.id, // $2
            update.company, // $3
        ];

        console.log('Executing query with:', parameters);
        await this.talentRepository.query(query, parameters);
    }
}

关键修改点 #

• 把jsonb_array_elements(experience)改回unnest(experience)：匹配jsonb[]类型的数组展开需求
• 删除array_agg(updated_elem)::jsonb[]中的强制类型转换：避免格式解析错误

性能优化建议 #

可批量处理所有更新项，减少数据库交互次数：

async updateExperience(talentId: string, experienceUpdates: ExperienceDto[]): Promise<void> {
    const updatesJson = JSON.stringify(experienceUpdates);
    
    const query = `
        WITH expanded AS (
            SELECT
                id,
                unnest(experience) AS elem
            FROM user_competences
            WHERE id = $1
        ),
        update_map AS (
            SELECT
                (elem->>'id') AS exp_id,
                (elem->>'company') AS new_company
            FROM jsonb_to_recordset($2::jsonb) AS elem(id text, company text)
        ),
        updated AS (
            SELECT
                e.id,
                CASE
                    WHEN um.new_company IS NOT NULL THEN jsonb_set(e.elem, '{company}', to_jsonb(um.new_company))
                    ELSE e.elem
                END AS updated_elem
            FROM expanded e
            LEFT JOIN update_map um ON e.elem->>'id' = um.exp_id
        )
        UPDATE user_competences
        SET experience = (
            SELECT array_agg(updated_elem)
            FROM updated
            WHERE updated.id = user_competences.id
        )
        WHERE id = $1;
    `;

    const parameters = [talentId, updatesJson];
    await this.talentRepository.query(query, parameters);
}

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