Postgresql: how to convert a key value table into json without using an intermediate hstore

Question

I have a key-value postgresql table with this form

pk | fk | key | value
---|----|-----|------
1  | 11 | k1  | v1
2  | 11 | k2  | v2
3  | 22 | k3  | v3
4  | 33 | k1  | v1
5  | 33 | kk  | vk
6  | 33 | kn  | vn

that i need to convert to json objects grouped by fk:

fk | kv
---|------------
11 | {"k1": "v1", "k2": "v2"}
22 | {"k3": "v3"}
33 | {"k1": "v1", "kk": "vk", "kn": "vn"}

I've already found a way to do this using and intermediate hstore conversion:

SELECT fk, hstore_to_json(hstore(array_agg(key), array_agg(value))) as kv
FROM tbl
GROUP BY fk, pk;

the issue is, hstore extension is not available for me on production, and I can't install it, is there another way to get the same form of output?

PS: postgresql version is 9.4.8

Patrick · Accepted Answer · 2016-06-12 11:03:54Z

5

It's actually very similar to the hstore version:

SELECT fk, json_object(array_agg(key), array_agg(value)) AS kv
FROM tbl
GROUP BY fk
ORDER BY fk;

Yields:

fk |                 kv
---+------------------------------------------
11 | '{"k1" : "v1", "k2" : "v2"}'
22 | '{"k3" : "v3"}'
33 | '{"k1" : "v1", "kk" : "vk", "kn" : "vn"}'

answered Jun 12, 2016 at 11:03

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abugnais Over a year ago

this worked for me, I didnt pay attention to this constructor json_object(keys text[], values text[])

Collectives™ on Stack Overflow

Postgresql: how to convert a key value table into json without using an intermediate hstore

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related