17.06.2013

Tool für Umzug für Mailkonten

Mailstore Home

http://www.mailstore.com/de/mailstore-home.aspx

16.06.2013

JSON Datenbank

Postgressql ab Version 9.3
http://www.enterprisedb.com/products-services-training/pgbindownload
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
CREATE TABLE aa (a int, b json);
CREATE TABLE aa (a int, b varchar(9000));
INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
INSERT INTO aa VALUES (2, '{"Response":{"MetaInfo":{"Timestamp":"2013-06-14T14:27:51.580+0000"},"View":[{"_type":"SearchResultsViewType","ViewId":0,"Result":[{"Relevance":1.0,"MatchLevel":"houseNumber","MatchQuality":{"City":1.0,"Street":[1.0],"HouseNumber":1.0,"PostalCode":1.0},"MatchType":"pointAddress","Location":{"LocationId":"NT_iFnXKE181rtRfm56s4Pm6B_10","LocationType":"point","DisplayPosition":{"Latitude":50.9767799,"Longitude":10.3006897},"NavigationPosition":[{"Latitude":50.9766884,"Longitude":10.3007097}],"MapView":{"TopLeft":{"Latitude":50.9779041,"Longitude":10.2989043},"BottomRight":{"Latitude":50.9756558,"Longitude":10.3024751}},"Address":{"Label":"Siebenbornstraße 10, 99817 Eisenach, Deutschland","Country":"DEU","State":"Thüringen","County":"Eisenach","City":"Eisenach","Street":"Siebenbornstraße","HouseNumber":"10","PostalCode":"99817","AdditionalData":[{"value":"Deutschland","key":"CountryName"},{"value":"Thüringen","key":"StateName"}]}}}]}]}}');
image
select (b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition') from aa
image
image
insert into aa (a,b) select a,to_json(b) from ab
to_json ist falsch, einfacher cast auf  json
insert into aa (a,b) select a, json(b) from ab

select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude'),
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude')
from aa order by a limit 20
select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude') as Lat,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude' ) as Lon
from aa
order by a


select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude') as Lat0,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude' ) as Lon0,
(b->'Response'->'View'->0->'Result'->0->'Relevance' ) as relevance0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'City' ) as CityMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'Street' ) as StreetMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'HouseNumber' ) as HousenumberMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'PostalCode' ) as PostalCodeMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'District' ) as DistsictMQ0

from aa
limit 100


select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude') as Lat0,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude' ) as Lon0,
(b->'Response'->'View'->0->'Result'->0->'Relevance' ) as relevance0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'City' ) as CityMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'Street' ) as StreetMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'HouseNumber' ) as HousenumberMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'PostalCode' ) as PostalCodeMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'District' ) as DistsictMQ0,
(b->'Response'->'View'->0->'Result'->0->'Location'->'Address'->'Label') as AdressMQ0,

(b->'Response'->'View'->0->'Result'->1->'Location'->'DisplayPosition'->'Latitude') as Lat1,
(b->'Response'->'View'->0->'Result'->1->'Location'->'DisplayPosition'->'Longitude' ) as Lon1,
(b->'Response'->'View'->0->'Result'->1->'Relevance' ) as relevance1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'City' ) as CityMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'Street' ) as StreetMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'HouseNumber' ) as HousenumberMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'PostalCode' ) as PostalCodeMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'District' ) as DistsictMQ1,
(b->'Response'->'View'->0->'Result'->1->'Location'->'Address'->'Label') as AdressMQ1,

(b->'Response'->'View'->0->'Result'->2->'Location'->'DisplayPosition'->'Latitude') as Lat2,
(b->'Response'->'View'->0->'Result'->2->'Location'->'DisplayPosition'->'Longitude' ) as Lon2,
(b->'Response'->'View'->0->'Result'->2->'Relevance' ) as relevance2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'City' ) as CityMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'Street' ) as StreetMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'HouseNumber' ) as HousenumberMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'PostalCode' ) as PostalCodeMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'District' ) as DistsictMQ2,
(b->'Response'->'View'->0->'Result'->2->'Location'->'Address'->'Label') as AdressMQ2,

(b->'Response'->'View'->0->'Result'->3->'Location'->'DisplayPosition'->'Latitude') as Lat3,
(b->'Response'->'View'->0->'Result'->3->'Location'->'DisplayPosition'->'Longitude' ) as Lon3,
(b->'Response'->'View'->0->'Result'->3->'Relevance' ) as relevance3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'City' ) as CityMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'Street' ) as StreetMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'HouseNumber' ) as HousenumberMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'PostalCode' ) as PostalCodeMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'District' ) as DistsictMQ3,
(b->'Response'->'View'->0->'Result'->3->'Location'->'Address'->'Label') as AdressMQ3,

from aa

limit 20