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


FROM json_test;


        "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 

Then I could query the view with something like this

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)

2 Answers 2


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

**If you are popular the bank with a json already getting the data


insert into person(name,Country,hobby,address,sex) 
select name,Country,hobby,address,sex
from json_populate_recordset(null::person, 
 {"name":"Roy","Country":"USA","hobby":"Swim","address":"Church Street","sex": "M" },
 {"name":"David","Country":"USA","hobby":"Swim","address":"Church Street","sex": "M" }
  • 1
    The original question is asking how to construct a view on existing stored JSON data that returns it in a standard row/column format. You're showing how you can insert data in JSON format into a regular table, which is a different thing.
    – RDFozz
    Commented Mar 27, 2018 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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