Update field nestjs jsonb[] postgresql

Question

I need update field in my i have something like that from 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"
}

now i try update this field by patch in postman for now, later in frontend. I prepare something like this in pgadmin

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';

and thats work, but now when i try do it in service in nest.js

    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
    }

that doesnt work i have error

{
    "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"
}

I know how it should be work, it should be something like unpack my jsonb array change fields and pack again, but i dont know how to do it or what change to finish that

I try update field by nestjs in jsonb array, i try by pgadmin with sql and works, but i cant do it in nest js