json_each的postgres逆 [英] postgres inverse of json_each

问题描述

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

I don't have json text in a field. I have a row with columns "text","format","keyname","..." and I want to export it to json in the format '{"value of keyname column": {"text":"value of text","format":"value of format",... }}'. Is that possible in postgresql?

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

I have been looking at a postgis2geojson example and learned about the row_to_json function already. http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

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

Furthermore I have been looking at the postgres json documentation: http://www.postgresql.org/docs/9.3/static/functions-json.html

不幸的是，我发现没有功能可以明确满足我的需求，但是json_each却在相反的方向上实现了我想要的功能.通常，我认为postgres中的json函数是在仅列名可用作键的前提下设计的.我对吗?有没有可以用来解决这个问题的SQL技巧?感谢您的帮助.

Unfortunately I found no function that covers my need explicitly but json_each does what I want just in the opposite direction. Generally, I think the json functions in postgres are designed on the assumption that only the column names can be used as keys. Am I right? Is there a SQL hack I could use to come around this? Thanks for any help.

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;

推荐答案

自9.4版起，postgres具有json_object_agg功能，与json_each相反.您可以在此处阅读文档.

Since version 9.4 postgres has json_object_agg function, which is the inverse of json_each. You can read the documentation here.

如果数据如下所示:

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

然后您可以使用键a和b通过以下查询将所有这些聚合到一个对象中:

Then you can aggregate all of that into an object with keys a and b with the following query:

> 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)

这篇关于json_each的postgres逆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！