JSON (JavaScript Object Notation) is a message exchange format that is commonly used by APIs (Application Programming Interface), and supported by many programming languages. As a relational database management system, MySQL natively supports the import and storage of JSON documents. One such way of importing a JSON document into MySQL is by converting JSON to tabular data with the JSON_TABLE(expression, path) function. The first argument, expression, returns JSON data, while the second argument, path, specifies a JSON path that is applied to the JSON document during conversion.
As an example, let us consider the following sample user data from DummyJSON ( https://dummyjson.com/users/1) given as a JSON document:
{"address": {"address": "1745 T Street Southeast","city": "Washington","coordinates": {"lat": 38.867033,"lng": -76.979235},"postalCode": "20020","state": "DC"},"age": 50,"bank": {"cardExpire": "06/22","cardNumber": "50380955204220685","cardType": "maestro","currency": "Peso","iban": "NO17 0695 2754 967"},"birthDate": "2000-12-25","bloodGroup": "A−","company": {"address": {"address": "629 Debbie Drive","city": "Nashville","coordinates": {"lat": 36.208114,"lng": -86.58621199999999},"postalCode": "37076","state": "TN"},"department": "Marketing","name": "Blanda-O'Keefe","title": "Help Desk Operator"},"domain": "slashdot.org","ein": "20-9487066","email": "atuny0@sohu.com","eyeColor": "Green","firstName": "Terry","gender": "male","hair": {"color": "Black","type": "Strands"},"height": 189,"id": 1,"image": "https://robohash.org/hicveldicta.png","ip": "117.29.86.254","lastName": "Medhurst","macAddress": "13:69:BA:56:A3:74","maidenName": "Smitham","password": "9uQFF1Lh","phone": "+63 791 675 8914","ssn": "661-64-2976","university": "Capitol University","userAgent": "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/12.0.702.0 Safari/534.24","username": "atuny0","weight": 75.4}
This JSON document consists of an object at the root, that has attributes with primitive values and nested objects. From these attributes, we want to extract the user’s first name, last name, age, city of their residence, and job title. For that purpose, we use the following SQL query:
-- The JSON document to convert, i.e., `@document`, contains a single user taken from https://dummyjson.com/users/1.-- Convert `@document` to tabular data, extracting five selected attributes.SELECT *FROM JSON_TABLE(@document, '$' COLUMNS (first_name TEXT PATH '$.firstName',last_name TEXT PATH '$.lastName',age INT PATH '$.age',city TEXT PATH '$.address.city',job TEXT PATH '$.company.title')) AS User;
The JSON document from above is already available as a user-defined variable, such as @document, to keep the subsequent statement more readable. There, we select all columns (refer to line 4) that result from JSON_TABLE being called on @document.
For this, we specify a path using $ which represents the whole JSON document (refer to line 5). This path determines the part of our JSON document which sources a single row in the resulting tabular data. For each of these rows, we specify the columns that it should consist of using COLUMNS (refer to lines 6 through 10).
In our example, these are the five attributes that we set out to extract earlier. For first_name, last_name, and age, we access the attribute values directly by referring to their name using a dot notation like in JavaScript (refer to lines 6 through 8). For the columns city and job, the desired attribute values are nested inside of objects (refer to lines 9 and 10). However, the basic principle stays the same, such as accessing attributes through dot notation.
If our JSON document does not consist of a single user but also comprises of an array of users, we can still convert the JSON document to tabular data with the second statement from above and a slight modification to the row source path. Since the source of a row is no longer the whole document, but a user's object inside the array that constitutes the JSON document, we change the path from $ to $[*] (refer to line 5):
-- The JSON document to convert, i.e., `@document`, contains an array with five users taken from https://dummyjson.com/users?limit=5.-- Convert `@document` to tabular data, extracting five selected attributes.SELECT *FROM JSON_TABLE(@document, '$[*]' COLUMNS (first_name TEXT PATH '$.firstName',last_name TEXT PATH '$.lastName',age INT PATH '$.age',city TEXT PATH '$.address.city',job TEXT PATH '$.company.title')) AS User;
With the changed row source path, the above code snippet results in a table with five rows, each representing a user from the JSON document. Despite the change to the row source path, the statement is identical to the previous version but uses an updated user-defined variable, such as @document, that contains an array of five users rather than just a single user.