在PostgreSQL列中复制和转换文本

发布于 2025-01-21 16:43:38 字数 717 浏览 3 评论 0原文

假设我有一些JSON存储在PostgreSQL中的JSON，就像这样：

{"the": [0, 4], "time": [1, 5], "is": [2, 6], "here": [3], "now": [7]}

这是一个倒置的索引，显示了每个单词的位置，

the time is here the time is now

我想将第二个示例中的文本从单独的列中放置。我可以像这样将倒文的文本转换为python：

def convert_index(inverted_index):
    unraveled = {}
    for key, values in inverted_index.items():
        for value in values:
            unraveled[value] = key

    sorted_unraveled = dict(sorted(unraveled.items()))
    result = " ".join(sorted_unraveled.values())
    result = result.replace("\n", "")
    return result

但是我很想在PostgreSQL中进行此操作，因此我不会从一列中读取文本，在其他地方运行脚本，然后在单独的列中添加文本。有人知道有一种方法吗？我可以使用某种脚本吗？

原文

Let's say I have some JSON stored in postgresql like so:

{"the": [0, 4], "time": [1, 5], "is": [2, 6], "here": [3], "now": [7]}

This is an inverted index showing the position of each word, which spells out

the time is here the time is now

I want to put the text from the second example in a separate column. I can convert the inverted text with python like so:

def convert_index(inverted_index):
    unraveled = {}
    for key, values in inverted_index.items():
        for value in values:
            unraveled[value] = key

    sorted_unraveled = dict(sorted(unraveled.items()))
    result = " ".join(sorted_unraveled.values())
    result = result.replace("\n", "")
    return result

But I would love to do this within postgresql so I am not reading text from one column, running a script somewhere else, then adding text in a separate column. Anybody know of a way to go about that? Can I use some kind of script?

分享到QQ

分享到微博

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

发布评论

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

蓝眼泪 2025-01-28 16:43:39

您需要使用jsonb_each（）获得键，并用JSONB_ARRAY_ELEMENTS（）将数组解开包装，然后用适当的顺序汇总键：

with my_table(json_col) as (
values
('{"the": [0, 4], "time": [1, 5], "is": [2, 6], "here": [3], "now": [7]}'::jsonb)
)

select string_agg(key, ' ' order by ord::int)
from my_table
cross join jsonb_each(json_col)
cross join jsonb_array_elements(value) as e(ord)

在 db＆lt;

You need to get keys with jsonb_each() and unpack arrays with jsonb_array_elements() then aggregate the keys with proper order:

with my_table(json_col) as (
values
('{"the": [0, 4], "time": [1, 5], "is": [2, 6], "here": [3], "now": [7]}'::jsonb)
)

select string_agg(key, ' ' order by ord::int)
from my_table
cross join jsonb_each(json_col)
cross join jsonb_array_elements(value) as e(ord)

Test it in Db<>fiddle.

回复收藏 0 原文

~没有更多了~