node.js - How to update a key of object with value in json column with knexjs?

Question

I'm trying to update a column in users table the column type is json.

column name is test.

and the column consists of an object default value for example is

{a: "text", b: 0}

how to update let's say the object key b without changing the whole column

the code i'm using is

knexDb('users').where({
  email: email
})
.update({
  test: { b: 1 }
})

second solution

knexDb('users').where({
  email: email
})
.update({
  test: knexDb.raw(`jsonb_set(??, '{b}', ?)`, ['test', 1])
})

first solution changes the whole column cell and test will be only { b: 1 }

second solution doesn't work it give an error

function jsonb_set(json, unknown, unknown) does not exist

The expected result

is to manage to update only a certain key value in an object without changing the whole object.

PS

I also want to update an array that consists of objects like the above one for example.

[{a: "text", b: 0}, {c: "another-text", d: 0}]

if i use the code above in kenxjs it'll update the whole array to only {b: 1}

PS after searching a lot found that in order to make it work i need to set column type to jsonb, in order the above jsonb_set() to work

but now i'm facing another issue

how to update multiple keys using jsonb_set

knexDb('users').where({
      email: email
    })
    .update({
      test: knexDb.raw(`jsonb_set(??, '{b}', ?)`, ['test', 1]),
      test: knexDb.raw(`jsonb_set(??, '{a}', ?)`, ['test', "another-text"]),
    })

the first query key b is now not updating, in fact all updates don't work except the last query key a, so can some explain why ?

标签： node.jsknex.js

Answer 1

您的问题是您正在覆盖test. 你传入update的是一个 JS 对象（docs）。您不能有多个具有相同值的键（docs）。你必须做这样的事情，你用你所有的原始 SQL 作为值来制作 1 个长字符串test。

knexDb('users').where({
      email: email
    })
    .update({
      test: knexDb.raw(`
          jsonb_set(??, '{a}', ?)
          jsonb_set(??, '{b}', ?)
        `,
        ['test', "another-text", 'test', 1])
    })

可能存在更好的选择 - 如果您必须为多个列执行此操作，那么它的可读性会更高，就像我在下面包含的那样。在此示例中，包含 的列jsonb称为json。

const updateUser = async (email, a, b) => {

  const user = await knexDb('users')
    .where({ email })
    .first();

  user.json.a = a;
  user.json.b = b;

  const updatedUser = await knexDb('users')
    .where({ email })
    .update(user)
    .returning('*');

  return updatedUser;
}