PostgreSQL JSON Arrays – Joining Key and Value Pairs

arrayjsonpostgresql

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

Best Answer

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

Related Solutions

PostgreSQL – Query JSON Array of Objects Against Multiple Values

There is a way: combine the containment operator @> with the ANY construct:

SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:

...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.

You can use an index for this:

Index for finding an element in a JSON array

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?

PostgreSQL JSON – Create Multi-Key JSON Object with Integers or Floats

Update

According to your sample data and desired result you can get it using json_object_agg Postgres function.

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);

select json_object_agg(str, num)
from test;

| json_object_agg        |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |

dbfiddle here

You can use row_to_json Postgres function.

create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);

select row_to_json(f)
from   (select f1,f2,f3,f4 from foo) f;

| row_to_json                                |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |

dbfiddle here

Best Answer

Related Solutions

PostgreSQL – Query JSON Array of Objects Against Multiple Values

PostgreSQL JSON – Create Multi-Key JSON Object with Integers or Floats

Related Question