Copy and convert text in postgresql column

Question

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?

Answer 1

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.