Hi I've recently discovered the PostgreSQL can be used store JSON before I import loads of data I need to understand how to retrieve it in particular the nested objects This is a good starting point but doesn't really explain how to query nested json array In the sample bellow I need to select the codes -> code where codes -> level: 1 (adminCode1_iso) is related to adminName1 and if it exists codes -> level: 2 is related to 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 ('') select info -> 'countryName' as countryName,info -> 'countr圜ode' as countr圜ode, info -> 'adminName1' as adminName1, info -> 'adminCode1' as adminCode1, info ->'codes' -> 'code' as adminCode1_iso FROM gn_json
0 Comments
Leave a Reply. |