JSON format incorrect?

[johnzastrow]

This JSON doesn't seem to load (issues format error) using 0.8.1 .exe. I can't tell if it's formatted correctly or not.

https://api.synopticlabs.org/v2/stations/timeseries?&token=20a6fb9bc24c4274806402b0c88371d3&start=201701011201&end=201701021201&timeformat=%25Y%25m%25d&obtimezone=local&units=english&output=json&stid=KPWM

[thombashi]

That data is valid as JSON, but unfortunately that schema is not supported for sqlitebiter. However, you might be able to workaround with extract convertible JSON data by jq command. What is your expected database schema for that JSON data?

[johnzastrow]

Thank you. I'm just interested in getting these data into flat tables in Sqlite. The database schema can be whatever will accommodate the input data. Sorry, where do I find this jq command?

[thombashi]

You can install via package manager or get executable binaries easily. Installation details for each environment is described official download page (https://stedolan.github.io/jq/download/).

[thombashi]

@johnzastrow

FYI: I've updated the dependency packages of sqlitebiter. So, you can convert another JSON Schemas. It still cannot convert the API result to a SQLite database file as it is. However, it is now easy to convert part of the API result.

Example:

Upgrade dependency packages

pip install sqlitebiter --upgrade

Filter API result example

$ cat timeseries.json | jq ".STATION[0].OBSERVATIONS" > filter_timeseries.json
$ sqlitebiter file filter_timeseries.json
[INFO] sqlitebiter file: convert 'filter_timeseries.json' to 'filter_timeseries_json1' table

Output

$ sqlite3 out.sqlite
sqlite> .schema
CREATE TABLE 'filter_timeseries_json1' ([airtemphigh24hourset1] INTEGER, [airtemphigh6hourset1] REAL, [airtemplow24hourset1] INTEGER, [airtemplow6hourset1] REAL, [airtempset1] REAL, [altimeterset1] REAL, [cloudlayer1codeset1] INTEGER, [cloudlayer2codeset1] INTEGER, datetime INTEGER, [dewpointtemperatureset1] REAL, [dewpointtemperatureset1d] REAL, [metaroriginset1] INTEGER, [metarset1] TEXT, [pressurechangecodeset1] INTEGER, [pressureset1d] REAL, [pressuretendencyset1] INTEGER, [relativehumidityset1] REAL, [sealevelpressureset1] REAL, [sealevelpressureset1d] REAL, [visibilityset1] INTEGER, [weatherconditionset1d] TEXT, [windcardinaldirectionset1d] TEXT, [windchillset1d] REAL, [winddirectionset1] INTEGER, [windgustset1] REAL, [windspeedset1] REAL);
sqlite> select * from filter_timeseries_json1 limit 10;
||||33.8|29.87|1002||20170101||30.15||METAR KPWM 011215Z AUTO 25007KT 10SM SCT100 01/M01 A2987||1008.89||86.48||1011.65|10|Partly Cloudy|WSW|27.01|250||7.0
||||33.8|29.87|1002||20170101||30.15||METAR KPWM 011240Z AUTO 23007KT 10SM SCT100 01/M01 A2987||1008.89||86.48||1011.65|10|Partly Cloudy|SW|27.01|230||7.0
||||33.8|29.87|1002||20170101||30.15||METAR KPWM 011250Z AUTO 22008KT 10SM SCT100 01/M01 A2987||1008.89||86.48||1011.65|10|Partly Cloudy|SW|26.37|220||8.01
||||33.98|29.87|1002||20170101|30.92|30.33|1|KPWM 011251Z 23009KT 10SM SCT100 01/M01 A2987 RMK AO2 SLP116  T00111006||1008.89||86.48|1011.6|1011.65|10|Partly Cloudy|SW|26.01|230||9.0
||||33.8|29.88|1||20170101||30.15||METAR KPWM 011255Z AUTO 22008KT 10SM CLR 01/M01 A2988||1009.23||86.48||1011.99|10|Clear|SW|26.37|220||8.01
||||33.8|29.88|1||20170101||30.15||METAR KPWM 011300Z AUTO 22010KT 10SM CLR 01/M01 A2988||1009.23||86.48||1011.99|10|Clear|SW|25.27|220||9.99
||||33.8|29.88|1||20170101||30.15||METAR KPWM 011305Z AUTO 23009KT 10SM CLR 01/M01 A2988||1009.23||86.48||1011.99|10|Clear|SW|25.79|230||9.0
||||33.8|29.88|1||20170101||30.15||METAR KPWM 011310Z AUTO 22008KT 10SM CLR 01/M01 A2988||1009.23||86.48||1011.99|10|Clear|SW|26.37|220||8.01
||||33.8|29.88|1||20170101||30.15||METAR KPWM 011315Z AUTO 22009KT 10SM CLR 01/M01 A2988||1009.23||86.48||1011.99|10|Clear|SW|25.79|220||9.0
||||35.6|29.89|1||20170101||30.11||METAR KPWM 011330Z AUTO 23009KT 10SM CLR 02/M01 A2989||1009.57||80.49||1012.32|10|Clear|SW|28.02|230||9.0

[thombashi]

@johnzastrow I'll close the issue. Since, elapsed a month from the last comment. Feel free to reopen.