Simple Solutions
From what I can determine, there are two simple ways. One is from a login shell command line in non-interactive mode, and the other is to (ab)use MySQL Shell's \pager command.
Login Shell
The login shell way is what you would do to capture any command output, along with specific mysqlsh arguments to run the query. For a Unix shell:
sql="SELECT table_catalog, table_schema, table_name, engine, create_time, table_collation
FROM tables
WHERE table_schema='INFORMATION_SCHEMA' AND table_name LIKE 'T%';"
mysqlsh -u user --sql --result-format=json/array --schema=INFORMATION_SCHEMA --execute "$sql" > results.json
For PowerShell, the variable would be set differently (i.e. with sv sql "..."), but the rest would be the same.
\pager Trickery
The old mysql client has a tee command that could send output to a file. While MySQL Shell has no similar command, \pager can be set to a (login shell) command that will output the results to a file. If you want to see the results as well, use whatever tee command the system has.
\option resultFormat json/array
\pager tee results.json
\use information_schema
SELECT table_catalog, table_schema, table_name, engine, create_time, table_collation
FROM tables
WHERE table_schema='INFORMATION_SCHEMA' AND table_name LIKE 'T%';
If you don't want to see the results, use a login shell command that produces no output, such as \pager cat >results.json or \pager type >results.json.
Note that each query result will replace the previous one (with a bit of trickery, you could probably create a command that would pick a file name that doesn't exist each time it's run).
Note also that the output will include the result summary ("<N> rows in set (<time> sec)") at the end. If you don't want this, either remove it from the file, or pipe the results through a command that will remove it before outputting to a file (e.g. grep -E -v '^[0-9]+ rows in set \([.0-9]+ sec\)$' | tee results.json or Select -SkipLast 1 | Tee-Object results.json; see "Opposite of tail: all lines except the last n lines" for more Unix options).
You can still pipe the results through a pager, if you want to view paged results. For example:
\pager sed '$d' | tee results.json | less
Complex Solutions
Python
Interactively, Python can be used, but only with appropriately defined helpers. As this solution relies on coding, it's potentially off-topic for DBA.SE and more suited for SO. However, the alternative of creating a related question on SO for this solution borders on cross-posting and splinters the answer, so the solution is presented here.
In programmatic modes (JS or Python), query results are Result or ClassicResult objects. MySQL Shell will format these when displaying them, but the results themselves have no format. This means in any programmatic mode, the results would have to be formatted programmatically before being output.
The Python engine has open, and the json module is available. Between the two of these, suitable functions can be defined to format results as JSON and output them to a file. json.JSONEncoder doesn't support Result and ClassicResult, so either a suitable JSONEncoder would need to be defined, or they'll need to be converted to built-in types, as will any fields that aren't built-in types (e.g. Date columns). The latter can be done fairly succinctly.
First, fields. A simple method to tell whether json can encode a field is to try it and handle the failure by converting the value to a string. Additionally, the field is to be fetched by name from the row using get_field.
def row_field(row, name):
"""Get a field by name from a row, converting it to a built-in type if necessary."""
field = row.get_field(name)
try:
json.dump(row.get_field(name))
except:
field = str(field)
return field
Converting to a built-in is fairly straight forward: the results must be fetched, and then list and dict comprehensions will assemble the row data into built-in types, using the above row_field() to fetch & convert fields.
def nativize(results):
"""Convert SQL query results to built-in types."""
rows = results.fetch_all()
return [{name:row_field(row, name) for name in results.column_names} for row in rows]
json.dump() will convert supported objects to JSON and output to a given file object. To tie everything together, here's a function to convert results, open a file and pass them to json.dump().
def dump_json(results, file=None, **kwargs):
"""Dump (or return) results as JSON to a file.
If no `file` is given, returns JSON results as string.
"""
data = nativize(results)
if file:
fp = open(file, 'w')
json.dump(data, fp, **kwargs)
fp.close()
else:
return json.dumps(data, **kwargs)
The above functions can be added to a plugin starting with version 8.0.17. In the 'plugins' directory on your platform, place the above functions (and an import json statement) into the init script in a plugin folder (e.g. 'dump_json/init.py'), along with the following to create an extension and add the file output function (adapted from the example plugin):
if 'ext' not in globals():
ext = shell.create_extension_object()
shell.register_global("ext", ext, {"brief":"MySQL Shell extension plugins."})
try:
shell.add_extension_object_member(ext, "dump_json", dump_json, {
'brief': 'Writes or returns the JSON representation of results to a given file.',
'parameters': [
{
'name': 'results',
'type': 'object',
'brief': 'Query results.',
},
{
'name': 'file',
'type': 'string',
'brief': 'output pathname',
'required': False,
},
]
})
except Exception as e:
shell.log("ERROR", f'Failed to register util.dump_json: {e}.')
The dump_json function can then be accessed via the ext global in MySQL Shell:
\py
query="""SELECT table_catalog, table_schema, table_name, engine, create_time, table_collation
FROM tables
WHERE table_schema='INFORMATION_SCHEMA' AND table_name LIKE 'T%';
"""
\use information_schema
results = session.run_sql(query)
ext.dump_json(results, 'path/to/results.json')
If using a version older than 8.0.17, instead create a Python module with the helper functions (the code to create an extension ext and add dump_json to it should be left out), and import the module from within MySQL shell.
Dead Ends
From within an interactive shell, INTO OUTFILE is handled by the server, so doesn't have the chance to be formatted by MySQL Shell. More generally, SQL is interpreted server-side and doesn't have a way of directing a client to save results to a file, so interactive SQL mode in MySQL Shell offers no solution on its own.
JavaScript doesn't have a standard way (either built-in, or from a library) of accessing files. The MySQL JS API offers a few of its own methods, such as reading text files via os and util's various file I/O methods, but not general file output. util misses the mark on two counts: though it has a few file output methods, they can only dump databases & tables (not arbitrary query results) and only in the standard dump format. (One method of general interest, though, is util.importJSON, which supports importing a JSON file into a document store.)
Additionally, JavaScript mode doesn't appear to have the standard JSON object to produce JSON output.
