如何使用knexjs更新json列中具有值的对象的键? [英] How to update a key of object with value in json column with knexjs?

问题描述

我正在尝试更新用户表中的列，列类型为 json .

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

列名是test.

，该列由对象默认值组成，例如，

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

{a: "text", b: 0}

如何更新而不用更改整个列为对象键b

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

我正在使用的代码是

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

第二个解决方案

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

第一个解决方案将更改整个列单元格，并且测试将仅为{ b: 1 }

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

第二个解决方案不起作用，它会给出错误消息

second solution doesn't work it give an error

函数jsonb_set(json，未知，未知)不存在

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

预期结果

用于在不更改整个对象的情况下仅更新对象中的某个键值.

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}]

如果我在kenxjs中使用上面的代码，它将把整个数组更新为仅{b: 1}

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

PS 经过大量搜索后发现，为了使其能够正常工作，我需要将列类型设置为jsonb，才能使上述jsonb_set()正常工作

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

如何使用jsonb_set更新多个密钥

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"]),
    })

第一个查询键b现在不更新，实际上除最后一个查询键a之外所有更新都无效，因此可以解释一下原因吗?

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 ?

推荐答案

您的问题是您正在覆盖test.您要传递给update的是一个JS对象(文档).您不能有多个具有相同值的键( docs ).您必须执行类似的操作，在其中使用所有原始SQL作为test的值来创建1个长字符串.

Your issue is that you're overwriting test. What you're passing into update is a JS object (docs). You cannot have multiple keys with identical values (docs). You'll have to do something like this where you make 1 long string with all your raw SQL as the value to test.

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

可能存在一个更好的选项-如果您必须对几列进行此操作，那么它的可读性会更高，就像我在下面介绍的那样.在此示例中，包含jsonb的列称为json.

Probably a better option exists - one that would be much more readable if you have to do this for several columns is something like what I have included below. In this example, the column containing the jsonb is called 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;
}

这篇关于如何使用knexjs更新json列中具有值的对象的键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！