Блог

Недавно я обнаружил, что PostgreSQL можно использовать для хранения JSON
, прежде чем импортировать множество данных, мне нужно понять, как их получить, в частности, вложенные объекты

Это руководство по postgresql является хорошей отправной точкой, но на самом деле не объясняет, как запрашивать вложенный массив json

В приведенном ниже примере мне нужно выбрать codes -> code, где codes -> level: 1 (adminCode1_iso) относится к adminName1 и, если он существует, codes -> level: 2 относится к adminName2

     CREATE TABLE gn_json (
   id serial NOT NULL PRIMARY KEY,
   info json NOT NULL
);
comment on table gn_json is 'How PG holds json';
insert into gn_json (info)
VALUES ('{
"adminCode2": "C3",
"codes": [
{
"code": "ENG",
"level": "1",
"type": "ISO3166-2"
},
{
"code": "CAM",
"level": "2",
"type": "ISO3166-2"
}
],
"adminCode3": "12UE",
"adminName4": "Yelling",
"adminName3": "Huntingdonshire",
"adminCode1": "ENG",
"adminName2": "Cambridgeshire",
"distance": 0,
"countryCode": "GB",
"countryName": "United Kingdom",
"adminName1": "England",
"adminCode4": "12UE085"
}',
'{
"codes": [
{
"code": "81",
"level": "1",
"type": "ISO3166-2"
}
],
"adminCode1": "63",
"distance": 0,
"countryCode": "TH",
"countryName": "Thailand",
"adminName1": "Krabi"
}');


select info ->> 'countryName' as countryName,info ->> 'countryCode' as countryCode,
info ->> 'adminName1' as adminName1, info ->> 'adminCode1' as adminCode1,
info ->> 'adminName2' as adminName2, info ->> 'adminCode2' as adminCode2,
info ->'codes->0->' -> 'code' as adminCode1_iso,
info ->'codes->1->' -> 'code' as adminCode2_iso
FROM gn_json;  
  

Редактировать ожидаемый результат

 countryname countrycode adminname1  admincode1  adminname2  admincode2  admincode1_iso  admincode2_iso
United Kingdom  GB  England ENG Cambridgeshire  C3  ENG CAM
Thailand    TH  Krabi   63  NULL    NULL    81  NULL