PostgreSQL JSON 數據類型用作 NOSQL，但 VIEW 用作關係資料結構

我可以從 JSON 數據類型創建一個 VIEW 但將其作為關係結構嗎？

例子：

SELECT * 
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"
   }
]

然後創建 VIEW 就像（不確定是否/如何做到這一點）

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

然後我可以用這樣的東西查詢視圖

SELECT * 
FROM normalized

結果集

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

由於 JSON 列中有多行，因此您需要一個返回集合的函式。這可以使用以下json_to_recordset()函式來完成：

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

因為這是一種匿名記錄類型，所以您必須明確定義每一列。如果你想把它放到一個視圖中，可能不是什麼大事。請注意，您的範例數據不會為Country您的 JSON 數據中的鍵返回值，只會返回命名的值，country因為列名和 JSON 鍵之間的匹配區分大小寫。

如果您不想創建視圖，可以使用自定義類型縮短上述內容：

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" }
]
');

引用自：https://dba.stackexchange.com/questions/98191