postgresql json_each 和 json_each_text 结果具有不同的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16650796/
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
json_each and json_each_text results with different column names
提问by ?lker ?nan?
I've just upgraded to Postgresql 9.3beta. When I apply json_eachor json_each_textfunctions to a json column, the result is a set of rows with column names 'key'and 'value'.
我刚刚升级到Postgresql 9.3beta。当我将json_each或json_each_text函数应用于 json 列时，结果是一组具有列名'key'和'value' 的行。
Here's an example:
下面是一个例子：
I have a table named customersand educationcolumn is of type json
我有一个名为的表customers，education列的类型json
Customers table is as follows:
客户表如下：
----------------------------------------------------------------------
| id | first_name | last_name | education |
---- ------------ ----------- ----------------------------------------
| 1 | Harold | Finch | {\"school\":\"KSU\",\"state\":\"KS\"} |
----------------------------------------------------------------------
| 2 | John | Reese | {\"school\":\"NYSU\",\"state\":\"NY\"} |
----------------------------------------------------------------------
The query
查询
select * from customers, json_each_text(customers.education) where value = 'NYSU'
returns a set of rows with the following column names
返回一组具有以下列名称的行
---------------------------------------------------------------------------------------
| id | first_name | last_name | education | key | value |
---- ------------ ----------- ---------------------------------------- -------- -------
| 2 | John | Reese | {\"school\":\"NYSU\",\"state\":\"NY\"} | school | NYSU |
---------------------------------------------------------------------------------------
because json_each_textfunction returns the set of rows with keyand valuecolumn names by default.
因为json_each_text函数默认返回带有key和value列名的行集。
However, I want json_each_textto return custom column names such as key1and key2:
但是，我想json_each_text返回自定义列名称，例如key1和key2：
-----------------------------------------------------------------------------------------
| id | first_name | last_name | education | key1 | value1 |
---- ------------ ----------- ---------------------------------------- -------- ---------
| 2 | John | Reese | {\"school\":\"NYSU\",\"state\":\"NY\"} | school | NYSU |
-----------------------------------------------------------------------------------------
Is there a way to get different column names like 'key1'and 'value1'after applying those functions?
有没有办法在应用这些函数后获得不同的列名，如“key1”和“value1”？
回答by michaelpq
You can solve that by using AS in FROM and SELECT clause:
您可以通过在 FROM 和 SELECT 子句中使用 AS 来解决这个问题：
postgres=# SELECT json_data.key AS key1,
json_data.value AS value1
FROM customers,
json_each_text(customers.education) AS json_data
WHERE value = 'NYSU';
key1 | value1
--------+--------
school | NYSU
(1 row)
