Sql update where for nested Json object

Question

I am trying to write an update query that updates any users that have a dog named "Mark", the table has a column named "additional_options" and it is a Json blob that looks like this

{
'dog_name': 'Mark'
}

Right now I have

update "Employees" e set country = "BR" where json_extract_path(e.additional_options, "dog_name") = "Mark"

I'm using this postgres functions found here to build this, but right now I'm getting an error

column "dog_name" does not exist

can anyone help me figure out why this isn't working?

Edit: If it helps, I wrote this query using Prisma and it works

export const moveToBrazil = async(): Promise<Employee[]> => {
    await db.employees.updateMany({
        where: {
            additional_options: {
                path: ['dog_name'],
                equals: 'Ellie'
            }
        },
        data: {
            country: "BR"
        }
    });
    return db.employees.findMany({
        where: {
            additional_options: {
                path: ['dog_name'],
                equals: 'Ellie'
            }
        },
    });
}

Answer 1

You can make use of ->> operator to get the json object field as text. Something like:

UPDATE "Employees" e SET country = 'BR' WHERE e.additional_options->>'dog_name' = 'Mark';

The ->> operator gets the JSON object field as text. If additional_options is a jsonb column and dog_name is a key in that JSON, additional_options->>'dog_name' gets the value of dog_name as text.

CODE DEMO

REFERENCE