PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

jsonnosqlpostgresqlrelational-theoryview

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)

Best Answer

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

Related Question