JSON
Hallo,
ich rufe per http Request Daten aus dem Web ab.
do as server
let response := http("GET", "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.GetStatement?q=" + Reference_Code + "&t=" + Token + "&v=2");
let xmlRohdaten := text(response.result);
let myJSON := parseXML(xmlRohdaten);
Rohdaten := text(myJSON)
end
Das Feld Rohdaten hat dann folgenden Inhalt:
{"FlexQueryResponse":{"@queryName":"Kurse","@type":"AF","FlexStatements":{"@count":"1","FlexStatement":
{"@accountId":"U740000","@fromDate":"21/01/2022","@toDate":"21/01/2022","@period":"LastBusinessDay","@whenGenerated":"24/01/2022032941","OpenPositions":{"OpenPosition":[{"@accountId":"U7400000","@acctAlias":"6000 - NR-Data","@symbol":"AMCR","@markPrice":"11.95"},{"@accountId":"U740000","@acctAlias":"6000 - NR-Data","@symbol":"AMGN","@markPrice":"227.72"},{"@accountId":"U740000","@acctAlias":"6000 - NR-Data","@symbol":"BLX","@markPrice":"16.53"},{"@accountId":"U740000","@acctAlias":"6000 - NR-Data","@symbol":"CSCO","@markPrice":"56.68"}]}}}}}
Ich würde diese Daten gerne in eine Tabelle Kurse mit den Feldern Symbol und markPrice schreiben. Ich bekomme es aber nicht hin, jeweils die Felder @symbol und @markPrice aus den Daten auszulesen und als neue Datensätze in die Tabelle zu schreiben. Hat jemand einen Tipp, wie ich das Lösen kann?
9 Antworten
-
Hallo HDS,
Ich würde die Werte direkt aus der Variable myJSON extrahieren.
---
let myArray:=myJSON.FlexQueryResponse.FlexStatements.FlexStatement.OpenPositions.OpenPosition;for i in myArray dolet new:=create TABELLENNAME;new.SYMBOLFELD:=i.'@symbol';new.MARKTPRICEFELD:='i.@markPrice'end
---
ansonsten statt myJSON einfach parseJSON(Rohdaten) nehmen
Leo
-
Eins noch: die @markPrice Felder enthalten die Werte als string. Wenn in deiner Tabelle das Preisfeld als Zahlenfeld ist, dann
new.MARKPRICEFELD:=number('i.@markPrice')
Leo
-
Wenn dein EWrgebnis in einem mehrzeilige Textfeld steht bekommst du mit:
parseJSON('Text (mehrzeilig)').FlexQueryResponse.FlexStatements.FlexStatement.OpenPositions.OpenPosition
in Array mit JSON-Objekten. In jedem einzelnen JSON-Objekt sind die gewünschten Werte enthalten.
Jetzt musst du mit item() und einer Schleife die gesuchten Werte aus jedem einzelnen JSON ziehen.
Beispiel für @symbol im ersten JSON-Oblekt:
item(parseJSON('Text (mehrzeilig)').FlexQueryResponse.FlexStatements.FlexStatement.OpenPositions.OpenPosition, 0).@symbol -
Klappt wie immer super! Vielen Dank.
-
zu früh gefreut ;-) Wenn das Objekt OpenPositions mehrfach vorkommt (z.B. weil die Abfrage mehrere Accounts betrift) bleibt die Variable myArray leer.
Bis let myArray:=myJSON.FlexQueryResponse.FlexStatements.FlexStatement werden noch Daten geschrieben. Ab
let myArray:=myJSON.FlexQueryResponse.FlexStatements.FlexStatement.OpenPositions dann nicht mehr.
Sobald es die OpenPositions nur einmal gibt, funktioniert mit let myArray:=myJSON.FlexQueryResponse.FlexStatements.FlexStatement.OpenPositions.OpenPosition alles problemlos.
-
Poste mal einen Response, mit dem Leo's Script nicht funktioniert.
-
Mache ich:
{"FlexQueryResponse":{"@queryName":"Aktuelle Kurse","@type":"AF","FlexStatements":{"@count":"5","FlexStatement":[{"@accountId":"U5929482","@fromDate":"24/01/2022","@toDate":"24/01/2022","@period":"LastBusinessDay","@whenGenerated":"25/01/2022065024","OpenPositions":{"OpenPosition":[{"@symbol":"ADEN","@markPrice":"45.36","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"200"},{"@symbol":"BALN","@markPrice":"154","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"BION","@markPrice":"63.25","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"200"},{"@symbol":"CMBN","@markPrice":"60.8","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"CSGN","@markPrice":"8.276","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"500"},{"@symbol":"HOLNz","@markPrice":"48.34","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"200"},{"@symbol":"NOVN","@markPrice":"76.75","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"OERL","@markPrice":"8.785","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"500"},{"@symbol":"SREN","@markPrice":"96.62","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"P NESN FEB 22 11600","@markPrice":"1.76","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"-1"},{"@symbol":"P SCHA MAR 22 23000","@markPrice":"19.29","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"-1"},{"@symbol":"P SLHN FEB 22 46000","@markPrice":"1.5","@currency":"CHF","@accountId":"U5929482","@reportDate":"24/01/2022","@position":"-1"}]}},{"@accountId":"U3259746","@fromDate":"24/01/2022","@toDate":"24/01/2022","@period":"LastBusinessDay","@whenGenerated":"25/01/2022065024","OpenPositions":{"OpenPosition":[{"@symbol":"BEP","@markPrice":"32.66","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"BLX","@markPrice":"16.56","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"200"},{"@symbol":"BMY","@markPrice":"62.61","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"20"},{"@symbol":"CSWC","@markPrice":"23.66","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"10"},{"@symbol":"EPR","@markPrice":"44.12","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"15"},{"@symbol":"IVZ","@markPrice":"21.57","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"200"},{"@symbol":"KMI","@markPrice":"17.32","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"KO","@markPrice":"59.96","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"10"},{"@symbol":"LAND","@markPrice":"29.66","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"MRK","@markPrice":"78.83","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"15"},{"@symbol":"MSM","@markPrice":"83.82","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"10"},{"@symbol":"NWBI","@markPrice":"14.41","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"OXY","@markPrice":"34.01","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"35"},{"@symbol":"PFE","@markPrice":"51.54","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"PM","@markPrice":"100.58","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"15"},{"@symbol":"SFL","@markPrice":"7.99","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"200"},{"@symbol":"TRTN","@markPrice":"60.94","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"10"},{"@symbol":"UNM","@markPrice":"25.65","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"10"},{"@symbol":"BEP 220218P00035000","@markPrice":"2.3776","@currency":"USD","@accountId":"U3259746","@reportDate":"24/01/2022","@position":"-1"}]}},{"@accountId":"U6761024","@fromDate":"24/01/2022","@toDate":"24/01/2022","@period":"LastBusinessDay","@whenGenerated":"25/01/2022065024","OpenPositions":{}},{"@accountId":"U3546056","@fromDate":"24/01/2022","@toDate":"24/01/2022","@period":"LastBusinessDay","@whenGenerated":"25/01/2022065024","OpenPositions":{"OpenPosition":[{"@symbol":"AGM","@markPrice":"124.82","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"10"},{"@symbol":"AMGN","@markPrice":"226.08","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"4"},{"@symbol":"KMB","@markPrice":"141.8","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"4"},{"@symbol":"KO","@markPrice":"59.96","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"20"},{"@symbol":"LAZ","@markPrice":"41.71","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"16"},{"@symbol":"MCD","@markPrice":"253.61","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"2"},{"@symbol":"PETS","@markPrice":"23.62","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"26"},{"@symbol":"PPL","@markPrice":"29.56","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"19"},{"@symbol":"QCOM","@markPrice":"170.07","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"2"},{"@symbol":"QSR","@markPrice":"55.75","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"20"},{"@symbol":"STAG","@markPrice":"41.74","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"52"},{"@symbol":"STT","@markPrice":"93.8","@currency":"USD","@accountId":"U3546056","@reportDate":"24/01/2022","@position":"10"}]}},{"@accountId":"U3123797","@fromDate":"24/01/2022","@toDate":"24/01/2022","@period":"LastBusinessDay","@whenGenerated":"25/01/2022065024","OpenPositions":{"OpenPosition":[{"@symbol":"BAYNd","@markPrice":"50","@currency":"EUR","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"CONd","@markPrice":"84.84","@currency":"EUR","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"100"},{"@symbol":"LHAd","@markPrice":"6.56","@currency":"EUR","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"540"},{"@symbol":"NIIN","@markPrice":"1.025","@currency":"EUR","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"500"},{"@symbol":"SPX 220218P02875000","@markPrice":"2.2525","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"3"},{"@symbol":"SPX 220318P01850000","@markPrice":"1.4262","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"3"},{"@symbol":"SPX 220318P02850000","@markPrice":"7.8748","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"3"},{"@symbol":"P SAP FEB 22 11000","@markPrice":"1.99","@currency":"EUR","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-1"},{"@symbol":"SPX 220218P02900000","@markPrice":"2.3688","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-2"},{"@symbol":"SPX 220218P03100000","@markPrice":"3.5775","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-2"},{"@symbol":"SPX 220318P01900000","@markPrice":"1.5964","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-2"},{"@symbol":"SPX 220318P02900000","@markPrice":"8.4251","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-2"},{"@symbol":"XLP 220218C00077000","@markPrice":"0.7645","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-1"},{"@symbol":"XLP 220218P00077000","@markPrice":"2.1086","@currency":"USD","@accountId":"U3123797","@reportDate":"24/01/2022","@position":"-1"}]}}]}}}
-
Ich würde es so schreiben:
if cnt(myJSON.FlexQueryResponse.FlexStatements.FlexStatement) > 1 then
let myArray1 := myJSON.FlexQueryResponse.FlexStatements.FlexStatement;
for y in myArray1 do
let myArray2 := y.OpenPositions.OpenPosition;
for i in myArray2 do
let new := (create JSON_Table);
new.(SYMBOLFELD := i.@symbol);
new.(MARKTPRICEFELD := i.@markPrice)
end
end
else
let myArray := myJSON.FlexQueryResponse.FlexStatements.FlexStatement.OpenPositions.OpenPosition;
for i in myArray do
let new := (create JSON_Table);
new.(SYMBOLFELD := i.@symbol);
new.(MARKTPRICEFELD := number(i.@markPrice))
end
end -
Vielen Dank. Das sieht gut aus.
Content aside
- vor 2 JahrenZuletzt aktiv
- 9Antworten
- 345Ansichten