postgres inverse of json_each

Question

字段中没有json文本。我有一行列为"text","format","keyname","..."，我想将其导出为格式为'{"value of keyname column": {"text":"value of text","format":"value of format",... }}'的json。这在postgresql中可能吗？

我已经看了一个postgis2geojson的例子，并且已经了解了row_to_json函数。http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

此外，我一直在查看postgresjson文档：http://www.postgresql.org/docs/9.3/static/functions-json.html

不幸的是，我没有发现明确满足我需求的函数，但json_each却朝着相反的方向做了我想要的事情。一般来说，我认为postgres中的json函数是在只有列名可以用作键的假设下设计的。我说得对吗？有没有一个SQL破解我可以用来解决这个问题？谢谢你的帮助。

编辑：

select '"'||keyname||'":"'||row_to_json((select r from(Select text, format,
    (select username from my.users where users.id = table.uid) as username,
    machinename ) as r ))||'"'
    from my.table where id = 1;

Answer 1

由于9.4版本postgres具有json_object_agg函数，它是json_each的逆函数。您可以在此处阅读文档。

如果数据看起来像这样：

> select * from example;
 text | format | keyname 
------+--------+---------
 foo  | bar    | a
 dead | beef   | b
(2 rows)

然后，您可以使用以下查询将所有这些聚合到具有关键字a和b的对象中：

> select json_object_agg(keyname, json_build_object('text', text, 'format', format)) from example;
                                     json_object_agg                                      
------------------------------------------------------------------------------------------
 { "a" : {"text" : "foo", "format" : "bar"}, "b" : {"text" : "dead", "format" : "beef"} }
(1 row)

Answer 2

为了保持API的清洁，我实际上会做一些不同的事情。你现在有这个：

select '"'||keyname||'":"'||row_to_json((select r from(Select text, format,
    (select username from my.users where users.id = table.uid) as username,
    machinename ) as r ))||'"'
    from my.table where id = 1;

你会有这个：

WITH full_r AS (select keyname, text, format, username, machinename 
                  FROM my.table JOIN my.users ON users.id = table.uid
                  WHERE id = 1),
     small_r AS (select text, format, username, machinename FROM full_r)
SELECT f.keyname, row_to_json(s)
  FROM small_r s CROSS JOIN full_r f;

这假设只返回一行。如果使用多行，则需要内部联接而不是交叉联接。