4.6.5.2 Export Query Results to ND-JSON Files with JSON Syntax

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:

• Before You Begin

• JSON Syntax

• Parameter Descriptions for URI

• Parameter Descriptions for PAR URL

• Parameter Descriptions for Resource Principal

• Example for URI

• Example for PAR URL

• Example for Resource Principal

• What's Next

Before You Begin

• Review the following:

  • Overview of Exporting Query Results

  • Requirements to Export Query Results

JSON Syntax

Use the following SELECT INTO JSON syntax to export the query results into an ND-JSON file.

SELECT ... INTO OUTFILE WITH PARAMETERS
'file_URI_JSON "file":
  begin-array ("uri": "OCIFS_URI") | ("uri": "PAR_URI") | ("uri": "NATIVE_URI"),
  [, "dialect": begin-object dialect_option [, dialect_option]... end-object ]
end-object'
'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"}
  }

Parameter Descriptions for URI

The following parameters are included in the JSON syntax to export the query results to the Object Storage location represented as a URI.

• URI: The Object Storage location is represented as a URI link. The URI must be a prefix URI and end in an unencoded / character.

  You can specify the following types of URIs.

  • OCIFS Syntax:

    oci://bucket_name@namespace_name/object_path

    Example:

    oci://bucket_1@tenant_1/data_files/

  • PAR URI Syntax. You can use one of the following formats:

    https://namespace_name.objectstorage.region_name.oci.customer-oci.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

    https://objectstorage.region_name.oraclecloud.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

    Example:

    https://tenant_1.objectstorage.us-ashburn-1.oci.customer-oci.com/p/.../n/tenant_1/b/bucket_1/o/data_files/

  • Native URI Syntax. You can use one of the following formats:

    https://namespace_name.objectstorage.region_name.oci.customer-oci.com/n/namespace_name/b/bucket_name/o/object_path

    https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/object_path

    Example:

    https://tenant_1.objectstorage.us-ashburn-1.oci.customer-oci.com/n/tenant_1/b/bucket_1/o/data_files/

• dialect: The dialect parameters assist you to organize the data in the exported CSV file.

Parameter Descriptions for PAR URL

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 in Oracle Cloud Infrastructure Documentation.

  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 to JSON format. You also have the option to set the record_delimiter parameter, which defines one or more characters to delimit records. The maximum field delimiter length is 64 characters. The default is "\\n".

Parameter Descriptions for Resource Principal

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.

• 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 to JSON format. You also have the option to set the record_delimiter parameter, which defines one or more characters to delimit records. The maximum field delimiter length is 64 characters. The default is "\\n".

Example for URI

The following example uses a specific OCIFS URI to export the query results as an ND-JSON file.

SELECT sum(l_quantity) from lineitem limit 1000
INTO OUTFILE WITH PARAMETERS
'{"file":[{
     "uri":"oci://bucket_1@tenancy1/data_files_1/"}], 
     "dialect": 
    {"format": "json"
    }
}';

Example for PAR URL

The following example uses a specific PAR URL to export the query results as an ND-JSON file.

SELECT 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"
    }
}';

Example for Resource Principal

The following example uses the specified resource principals to export the query results as an ND-JSON file.

SELECT 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"}
}';

What's Next

• Review the following:

  • Redacted PAR URLs in Exported Log Files

  • File Name Format for Exported Query Result Files