将JSONB字段从单个值转换为用自己的钥匙的对象

发布于 2025-01-23 21:54:15 字数 827 浏览 2 评论 0原文

我有一个表格，让我们称其为mytable使用以下结构，

ID   | data
____________
uuid | jsonb

JSONB字段中的数据是以下方式结构的数组：

[
  {
    "valueA": "500",
    "valueB": "ABC",
  },
  {
    "valueA": "300",
    "valueB": "CDE",
  }
]

我想做的是通过将valueb转换为一个对象来转换数据，而NewKey将其closposnds closposnds与“ valueb”的当前值

这是我想要的结果：

[
  {
    "valueA": "500",
    "valueB": {"newKey": "ABC"},
  },
  {
    "valueA": "300",
    "valueB": {"newKey": "CDE"},
  }
]

我尝试使用以下查询进行操作：

UPDATE myTable
SET data = (
    SELECT jsonb_agg (
        jsonb_insert(elems, '{valueB, newKey}', elems->'valueB')
    )
    FROM jsonb_array_elements(data) elems
);

不幸的是，它似乎并没有做任何事情。

我的另一个想法是创建一个新字段，将其初始化为对象，然后删除旧的ONDE并将其重命名为新字段，但是似乎必须有一种方法可以直接做我想做的事情？

原文

I have a table, let's call it myTable with the following structure

ID   | data
____________
uuid | jsonb

The data in the jsonb field is an array structured in the following way:

[
  {
    "valueA": "500",
    "valueB": "ABC",
  },
  {
    "valueA": "300",
    "valueB": "CDE",
  }
]

What I want to do is transform that data by converting valueB to be an object, with newKey that corresposnds to the current value of "valueB"

This is the result I want:

[
  {
    "valueA": "500",
    "valueB": {"newKey": "ABC"},
  },
  {
    "valueA": "300",
    "valueB": {"newKey": "CDE"},
  }
]

I tried doing it with the following query:

UPDATE myTable
SET data = (
    SELECT jsonb_agg (
        jsonb_insert(elems, '{valueB, newKey}', elems->'valueB')
    )
    FROM jsonb_array_elements(data) elems
);

It doesn't seem to do anything unfortunately.

Another idea I have is to create a new field, initialize it as an object, then delete the old onde and rename the new one, but it seems there must be a way to do what I want directly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

数理化全能战士 2025-01-30 21:54:15

使用 jsonb_build_object（）

UPDATE myTable
SET data = (
    SELECT jsonb_agg (
        jsonb_insert(elems, '{valueB}', jsonb_build_object('newKey', elems->'valueB'))
    )
    FROM jsonb_array_elements(data) elems
);

Solved using jsonb_build_object()

UPDATE myTable
SET data = (
    SELECT jsonb_agg (
        jsonb_insert(elems, '{valueB}', jsonb_build_object('newKey', elems->'valueB'))
    )
    FROM jsonb_array_elements(data) elems
);

回复收藏 0 原文

~没有更多了~