current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Could I create a VIEW from the JSON data type but have it as a relational structure?

Example:

SELECT * 
FROM json_test;

Results

[
    {
        "name": "Roy",
        "Country": "USA",
        "hobby": "Swim",
        "address": "Church Street",
        "sex": "M"
    },
    {
        "name": "Roy",
        "Country": "USA",
        "hobby": "Cricket",
        "address": "Amsterdam",
        "sex": "F"
    },
    {
        "name": "Anam",
        "country": "Greece",
        "hobby": "Polo",
        "address": "MG Road",
        "sex": "M"
    }
]

Then to create the VIEW would be something like (not sure if/how to do this)

CREATE VIEW normalized AS 
SELECT name, country, hobby, address, sex 
FROM JSON data

Then I could query the view with something like this

SELECT * 
FROM normalized

Result set

name  | country | hobby   | address       | sex  
------+---------+---------+---------------+----
 Roy  | USA     | Swim    | Church Street | M
 Roy  | USA     | Cricket | Amsterdam     | F
 Anam | Greece  | Polo    | MG Road       | M
(3 rows)
share|improve this question

1 Answer 1

up vote 4 down vote

As you have multiple rows inside the JSON column, you need a function that returns a set. This can be done using the json_to_recordset() function:

select j.*
from json_test, 
     json_to_recordset(json_data) as j(name text, country text, hobby text, address text, sex text);

Because this is an anonymous record type, you must explicitly define each column. Probably not a big thing if you want to put that into a view. Note that your sample data would not return a value for the Country keys in your JSON data, only for the one named country because the matching between column names and JSON keys is case-sensitive.

If you don't want to create a view, you can shorten the above using a custom type:

create type person_t as (name text, country text, hobby text, address text, sex text);

select j.*
from json_test, 
     json_populate_recordset(null::person_t, json_data) as j;

SQLFiddle: http://sqlfiddle.com/#!15/91b85/1

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.