Json_Each and Json_Each_Text Results with Different Column Names
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...
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?
1 Answer
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)
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
