Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Export JSON Data

Explore techniques for exporting JSON data from MySQL databases using SELECT INTO OUTFILE along with JSON_OBJECT and JSON_ARRAYAGG functions. Understand server-side file restrictions and how to format JSON output clearly. This lesson also covers alternate JSON export formats and common interview question variations.

We'll cover the following...

Solution

You can try it yourself using the steps below in the terminal:

  • Click “Click to Connect...” in the terminal widget to establish a connection.

  • The prepended command or start script will be executed when you connect to the terminal. It will start the MySQL service and have the setup for OnlineStore ready for your use.

  • Once you are inside the MySQL shell, select the OnlineStore database in both terminals by running the following command:

USE OnlineStore;

  • Run the query below to export a JSON file from the database:

SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
           'customerName', CustomerName,
           'emailAddress', Email,
           'tier', CustomerTier,
           'loyaltyPoints', LoyaltyPoints
         )
       )
FROM   Customers
WHERE  CustomerTier = 'VIP'
INTO OUTFILE '/var/lib/mysql-files/vip_customers.json'
LINES TERMINATED BY '';

  • Click the + icon next to Terminal 1 in the top left corner of the terminal pane to open Terminal 2.

cat /var/lib/mysql-files/vip_customers.json

Click on “Click to Connect...” in the terminal below to start.

Terminal 1
Terminal
Loading...

Recall of relevant concepts

We have covered the following concepts in this question:

  • Exporting query results to files using SELECT ... INTO OUTFILE

  • Server-side file restrictions using ...