json_each and json_each_text results with different column names

Question

I've just upgraded to Postgresql 9.3beta. When I apply json_each or json_each_text functions to a json column, the result is a set of rows with column names 'key' and 'value'.

Here's an example:

I have a table named customers and education column is of type 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_text function returns the set of rows with key and value column names by default.

However, I want json_each_text to return custom column names such as key1 and 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?

Answer 1

You can solve that by using AS in FROM and SELECT clause:

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)