I want to export from sql server result json to 'json file'.
Example:
SELECT * FROM SYS.all_columns FOR JSON AUTO
I know one method to do this using the command "BCP".
You are invited to share different ways to export 'Json File From Sql Server.'
(Example convert you can watch Here: convert table to Json)
To much for a comment, but only half an answer...
You know BCP, so nothing to tell you here...
Important to know: SQL-Server is very limited in its access to the file-system. It is not running as the user running the script. it is running in the context of the machine, where the SQL-Server is running: So a file destination like c:\temp\SomeFile.json might not get in the so called directory on your machine. A destination somewhere on a shared drive might fail with access violations.
It might be more flexible to use an external tool (power shell or any programming language of your choice) to connect to the database and call the result in order to store it.
If you need to trigger this from within SQL-Server, you can execute such an external programm using xp_cmdshell (just as you do this with BCP.
I'd suggest to create a VIEW, an UDF or a SP to return whatever you need in a single call (with some paramterers). This will allow you to control the content from within SQL-Server but to execute the retrieval and the storage externally.
Hope this helps...