CODEWITHSUNDEEP
jsonpostgresqlhstore
benstpierre
benstpierre

Reputation: 33581

A postgresql query that returns one column as json/name value pairs/etc

I have a query that goes has columns like this...

systemid | created | updated

for each system id there is an arbitrary set of name value pairs in a table that looks like this.

systemid | name | value

so this table might have

1,'name','ben'
1,'age',42
2,'name','john'
2,'age',22
2,'favoritecolor','red'

Does anybody know of a way to make a query return all name value pairs for a systemid as a name/value pair type? I'd like to get a result like this.

systemid | created | updated | profiledata

1,'name','ben','jan 1, 2012, 'name=>\'ben\',age=42'
2,'name','john','sept 15, 2011, 'name=>\'john\',age=22, favoritecolor=>\'red\''

Upvotes: 0

Views: 1136

Answers (2)

Mark Amery
Mark Amery

Reputation: 154695

Any solution to this is roughly going to have one of the following forms:

SELECT table1.systemid,
       created,
       updated,
       some_aggregate_function(name, value) AS profiledata
FROM table1 JOIN table2 ON table1.systemid = table2.systemid
GROUP BY table1.systemid, created, updated

or

SELECT table1.systemid,
       created,
       updated,
       some_aggregate_function(some_function(name, value)) AS profiledata
FROM table1 JOIN table2 ON table1.systemid = table2.systemid
GROUP BY table1.systemid, created, updated

Here is a fairly hacky, ugly example of the second approach, which returns as the fourth column a JSON object with name/value pairs:

SELECT table1.systemid, 
       created,
       updated, 
       '{' || array_to_string(
         array_agg(
           '"' || name || '":"' || value || '"'
         ), ','
       ) || '}' AS profiledata
FROM table1
JOIN table2 
ON table1.systemid = table2.systemid
GROUP BY table1.systemid, created, updated;

If you want something prettier to achieve the same result, you might want to look into defining your own aggregate functions. I'm afraid I'm not any kind of SQL whiz so I can't come up with a nice, elegant solution off the top of my head, but hopefully this puts you on the right track.

Upvotes: 0

Glenn
Glenn

Reputation: 9150

I guess you could try something along these lines:

postgres=# CREATE TABLE attr(systemid int, name varchar(32), value varchar(64));
CREATE TABLE
postgres=#
postgres=# INSERT INTO attr VALUES(1,'name','ben');
INSERT 0 1
postgres=# INSERT INTO attr VALUES(1,'age', '42');
INSERT 0 1
postgres=# INSERT INTO attr VALUES(2,'name','john');
INSERT 0 1
postgres=# INSERT INTO attr VALUES(2,'age', '22');
INSERT 0 1
postgres=# INSERT INTO attr VALUES(2,'favoritecolor','red');
INSERT 0 1
postgres=#
postgres=# SELECT systemid
postgres-#       ,array_agg( name || '=' || value) AS profile_data
postgres-#   FROM attr
postgres-#   GROUP BY systemid;
 systemid |             profile_data
----------+--------------------------------------
        1 | {name=ben,age=42}
        2 | {name=john,age=22,favoritecolor=red}
(2 rows)

Or you can look into hstore or json data types.

Upvotes: 1

Related Questions