-1

I am working with a remote CentOS machine. I don't have graphical access, and I am just using a terminal. On the remote machine, I have a MySQL database with a table. I execute the command SELECT * FROM MY_TABLE LIMIT 10 to get the first 10 entries.

Now, I'd like to save this result in a JSON file on the machine. Eventually, I will do this for the whole table, not just 10 elements. I followed this tutorial, but it doesn't actually create a JSON file on the disk. How do I do this?

Software Dev
  • 910
  • 3
  • 10
  • 27
  • Didn't you kinda miss the creation of a JSON String output if your quesry is just what you wrote – RiggsFolly Sep 06 '19 at 17:34
  • One way I found using SEARCH was https://stackoverflow.com/questions/6332994/write-results-of-sql-query-to-a-file-in-mysql – RiggsFolly Sep 06 '19 at 17:35
  • And another https://stackoverflow.com/questions/36368425/how-can-i-execute-an-sql-file-and-save-the-results-to-a-txt-file-in-one-command – RiggsFolly Sep 06 '19 at 17:35
  • @RiggsFolly - This is not an "exact duplicate". The key difference is "JSON". The OP needs to convert the data to JSON first; the dup does not do that. – Rick James Sep 06 '19 at 19:08
  • The additional problem is that older versions of MySQL and MariaDB have no way to create JSON. (At least not without lots of ugly CONCATs.) – Rick James Sep 06 '19 at 19:10
  • So, the first question is "What version are you running?" – Rick James Sep 06 '19 at 19:10

1 Answers1

0

If you have a suitably new version of MySQL (8.0), and you can craft a JSON expression with JSON_ARRAYAGG, JSON_OBJECT, etc, then you can write that to a file by using the 

SELECT ... INTO OUTFILE '...' ...

syntax.

Rick James
  • 135,179
  • 13
  • 127
  • 222