0

I have two jsonb columns(keys,values). Eg: keys colum value = ["key1","key2","key3","key4"] values column = ["val1","val2","val3","val4"]

I want to write a select query to get the output as below based on the array index.

{"key1":"val1","key2":"val2","key3":"val3","key4":"val4"}
1
  • This is existing table structure. Cannot change that. But output need as a single key value json
    – REMITH
    Commented May 6, 2021 at 6:44

1 Answer 1

1

You need to unnest the arrays then aggregate everything back into a single JSON value:

select x.value
from the_table t
  cross join lateral (
    select jsonb_object_agg(k.ky, v.value) as value
    from jsonb_array_elements_text(t.keys) with ordinality as k(ky,idx)
       join jsonb_array_elements(t.values) with ordinality as v(value,idx) on k.idx = v.idx
  ) x

Online example

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.