How can I update a jsonb field partially via `typeorm` in postgresql?

user_5421539 0
Viewed 2845

I am using typeorm (https://github.com/typeorm/typeorm) as ORM library to connect to postgresql database. I saved a few columns as jsonb type. Now I need to support update a json data partially, for example,

{
firstName: xx,
lastName: xxx
}

If I only update firstName, I don't want to override lastName in the update statement. postgresql supports json_set which I can use to update the field partially but how can I make it work with typeorm libraray?

typeorm node.js postgresql
2 Answers

Since jsonb datatype is specific to PostgreSQL you will need to use Postgres-specific raw SQL.

How to use raw SQL for update is explained under raw-sql-support in the TypeOrm documentation. For example:

    await getConnection()
        .createQueryBuilder()
        .update(User)
        .set({ jsonbColumn: () => "raw SQL jsonb function here" })
        .where("id = :id", { id: 2 })
        .execute();

Refer to jsonb_set under json functions and operators in PostgreSQL documentation for how to replace part of jsonb data:

jsonb_set(jsonbColumn,'{first_name}','"new first name"')

Putting it together, it will look like this:

let id = 2;
let pathText = '{first_name}';
let newValue  = '"new first name"';
await getConnection()
    .createQueryBuilder()
    .update(User)
    .set({ jsonbColumn: () => "jsonb_set(jsonbColumn," + pathText + ",'" + newValue + "')" })
    .where("id = :id", { id: id })
    .execute();

Those quotes needed by json and jsonb_set make this very awkward, and if your new name contains embedded " or ' it will mess up.

user_1586750

Just adding to the previous reply: If you want to add new fields as well you may try:

let keyName:string = '{key2}'
let valueName:string = '"new_value"'

emailLog: () => "jsonb_set(cast(email_log as jsonb), '" + keyNAme + "','" + valueName + "'," + "true" + ")"
user_113420970
Related