2

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)
5

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

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

Exmple:**

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 Mar 27 '18 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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