postgresql 在 row_to_json 函数中选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25564654/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select query in row_to_json function
提问by Vivek S.
For example ,
I use the following function to convert rowsinto jsonin PostgreSQL 9.2
例如，我用下面的函数转换rows成json中PostgreSQL 9.2
select row_to_json(row(productid, product)) from gtab04;
and this will returns below results
这将返回以下结果
row_to_json
---------------
{"f1":3029,"f2":"DIBIZIDE M TAB"}
{"f1":3026,"f2":"MELMET 1000 SR TAB"}
{"f1":2715,"f2":"GLUCORED FORTE"}
{"f1":3377,"f2":"AZINDICA 500 TAB"}
- unfortunately it loses the field names and replaces them with f1, f2, f3, etc.
- How can I get the actual field names or cast field name?
- 不幸的是，它丢失了字段名称并用 f1、f2、f3 等替换了它们。
- 如何获取实际字段名称或强制转换字段名称？
回答by Vivek S.
To work around this we must either create a row type and cast the row to that type or use a subquery. A subquery will typically be easier.
要解决此问题，我们必须创建一个行类型并将该行转换为该类型或使用子查询。子查询通常会更容易。
select row_to_json(t)
from (
select productid, product from gtab04
) t
回答by Tim
If one wants to prevent a sub-query, json_build_object()might be a solution. It does not map the column name, but let's your set the JSON keys explicitly.
如果想要阻止子查询，json_build_object()可能是一种解决方案。它不映射列名，但让我们明确设置 JSON 键。
Query
询问
SELECT json_build_object('productid', productid, 'product', product) FROM gtab04;
json_build_object
------------------
{"productid":3029,"product":"DIBIZIDE M TAB"}
{"productid":3026,"product":"MELMET 1000 SR TAB"}
{"productid":2715,"product":"GLUCORED FORTE"}
{"productid":3377,"product":"AZINDICA 500 TAB"}
