Import JSON - Githubissues

hankinsoft / SQLPro

SQLPro bug & features tracking.

100 stars 27 forks source link

Import JSON #924

Closed gabriel-r closed 11 months ago

gabriel-r commented 11 months ago

Is your feature request related to a problem? Please describe. I very often need to import data from Elasticsearch, where I can't get a clean CSV most of the time due to various limitations.

Describe the solution you'd like

An option to Import → JSON… on the same level as CSV.
I guess this would first covert the data in the most straightforward way to CSV, i.e. with jq or miller
Then the regular (CSV) import UI is presented.

Describe alternatives you've considered Using jq to convert the thing, with help from ChatGPT for the syntax. Painful.

Environment details (please complete the following information):

Device: MacBok M1
OS: macOS 13.4.1 (22F82)
SQLPro app Version: Version 2023.50 (Build 111050)
Installation source: Setapp
- Target database server: PostgreSQL

hankinsoft commented 11 months ago

Could you post (or email via Kyle @ hankinsoft.com with this request id) an example json file that you would like to import? That way I can test with it before posting a build. I really like the suggestion of converting it to a csv file, then doing the import via that - it makes this a pretty feasible suggestion.

gabriel-r commented 11 months ago

Well, that's the thing, it should work with any JSON file. The tools themselves have very good error checking and will report useful debug messages. And they don't choke even on files with 200k+ objects.

hankinsoft commented 11 months ago

Build has been on setapp for a bit. Please give it a try and let me know if works as expected.

gabriel-r commented 11 months ago

I've tried it quickly, and here are my findings:

GOOD: The import works well for "flat" JSON files. Even very large files seems are parsed nicely and fast.

BAD: The import will only convert the root level elements, and import nested fields as text.

So, great job, but please add support for nested JSON elements. The current version is only marginally useful, as the majority of JSON files (and the point of it is to) have nested fields.

PS: Consider adding a message in the UI, mentioning that "Only root fields are imported as columns, nested elements are currently not supported."

gabriel-r commented 11 months ago

PS: The nice support for importing CSV and JSON is a big deal, I'd suggest you add this to the home page https://www.sqlprostudio.com