This tutorial explains how to run SQL queries on JSON files. There is a free command line tool, trdsql that lets you do that. Using this tool, you can use the SQL statements to retrieve data from a JSON file and see the output. It allows you to save the output to another file as well if you want to. You can use it to get the data from JSON file in JSON format or in CSV or ASCII tables. The tool is portable and you don’t need anything further to use it. Also, apart from files, you can use it to query data from MySQL and PostgreSQL server if one running on your PC.
Before this, we have covered posts like executing SQL queries on CSV, and Excel files. But now, in this post, I will be discussing about how to do the same for JSON files. This tool can be helpful in your programming work as well. You can use it in backend of your applications to query JSON files. However, currently it doesn’t support UPDATE statements on files but you can use that command by cloning the JSON data into a database such as SQLite.
How to Run SQL Queries on JSON Files?
This is an open source tool which you can use to query JSON files from the command line through SQL. And there is no need to install it as there are prebuilt binaries for different platforms are available. Just download the EXE file for Windows from here and then put that in some directory of your choice. I will recommend you to put in C:/Windows folder for universal access.
After downloading, just open command prompt or terminal and then navigate to the folder where the JSON file is and that you want to query. After that, just start using the different forms of the SELECT statements to retrieve data from JSON.
You can retrieve data from JSON file in CSV format as well. For that, you just have to add “ijson” switch in the above command. See below.
Also, you can query only specific JSON values by treating them as columns. For that, you just run the SQL queries as shows in the following command.
This way, you can run SQL queries on JSON files using this simple command line tool, trdsql. You just download it for your platform and use it in the same ways. There are some additional command and switches out there hat you use it with it. Also, it can work with MySQL and PostgreSQL like database. You can see more about its commands on its GitHub page.
Final thoughts
trdsql is simple and a very nice tool to retrieve data from JSON files with ease. You can easily run it on any platform and in a very simple way. Use it to run SQL queries to retrieve data from any JSON file and even save the output. And I really like this tool as it can show the SQL output in CSV format. So, if you are looking for a simple tool that you can retrieve data from JSON files then this tool will be very helpful.
