The option to export query results as an ND-JSON file is available as of MySQL 9.3.1.
This topic shows the different ways to export query results as an ND-JSON file.
This topic contains the following sections:
-
Review the following:
Use the following SELECT
INTO
JSON syntax to export the query results
into an ND-JSON file.
Press CTRL+C to copySELECT ... INTO OUTFILE WITH PARAMETERS 'begin-object_PAR_JSON "file": begin-array "par": "par_url" [, "prefix": "prefix_value" ] end-array [, "dialect": begin-object dialect_option [, dialect_option]... end-object ] end-object' 'begin-object_RP_JSON "file": begin-array "bucket": "bucket_name" [, "prefix": "prefix_value" [, "namespace: "namespace_name" ] [,"region": "region_name" ] ] end-array [,"dialect": begin-object dialect_option [, dialect_option]... end-object ] end-object' dialect_option_key_value: { {"format": "json"} | {"record_delimiter": "\\n"} }
The following parameters are included in the JSON syntax to export the query results to the Object Storage location represented as a PAR URL.
-
URI
: You can use a PAR URL to access Object Storage. Learn more about Pre-Authenticated Request Requirements to export query results. To learn more about PARs, see Object Storage Pre-Authenticated Requests from the HeatWave on OCI Service Guide.For security reasons, the PAR URL is hidden in the logs file. For more information and samples, see Redacted PAR URLs in Exported Log Files.
dialect
: Set the exported file toJSON
format. You also have the option to set therecord_delimiter
parameter, which defines one or more characters to delimit records. The maximum field delimiter length is 64 characters. The default is"\\n"
.
The following parameters are included in the JSON syntax to export the query results to the Object Storage location based on the assigned resource principal. Learn more about Resource Principal Requirements.
bucket
: The bucket name.prefix
: The directory located within the bucket to store the export query results. This is an optional parameter.namespace
: The tenancy namespace.region
: The region that the tenancy is in.dialect
: Set the exported file toJSON
format. You also have the option to set therecord_delimiter
parameter, which defines one or more characters to delimit records. The maximum field delimiter length is 64 characters. The default is"\\n"
.
The following example uses a specific PAR URL to export the query results as an ND-JSON file.
Press CTRL+C to copySELECT sum(l_quantity) from lineitem limit 1000 INTO OUTFILE WITH PARAMETERS '{"file":[{ "par":"https://objectstorage.region1.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZderfUJAGUypxQb/n/tenancy1/b/usr-bucket/o/", "prefix":"summary/2025"}], "dialect": {"format": "json" } }';
The following example uses the specified resource principals to export the query results as an ND-JSON file.
Press CTRL+C to copySELECT sum(l_quantity) from lineitem limit 1000 INTO OUTFILE WITH PARAMETERS '{"file":[{"region": "region1", "namespace": "org_namespace", "bucket": "usr-bucket", "prefix": "prefix1/prefix2/ }], "dialect": {"format": "json"} }';
-
Review the following: