You can tabulate a JSON document (retrieve it as a row) in MySQL with the JSON_TABLE function.
NOTE: more info is available in the MySQL JSON functions documentation page
👉 Need a FREE MySQL database? check Aiven's free plans👈
The dataset
The dataset is the following:
{
"id": 778,
"shop": "Luigis Pizza",
"name": "Edward Olson",
"phoneNumbers":
["(935)503-3765x4154","(935)12345"],
"address": "Unit 9398 Box 2056 DPO AP 24022",
"image": null,
"pizzas": [
{
"pizzaName": "Salami",
"additionalToppings": ["🥓", "🌶️"]
},
{
"pizzaName": "Margherita",
"additionalToppings": ["🍌", "🌶️", "🍍"]
}
]
}
The following examples use a pizza order dataset with an order having:
-
id: 778 -
shop: "Luigis Pizza" -
name: "Edward Olson" -
phoneNumbers:["(935)503-3765x4154","(935)12345"] -
address: "Unit 9398 Box 2056 DPO AP 24022" -
image: null - and two pizzas contained in the
pizzasitem:
[
{
"pizzaName": "Salami",
"additionalToppings": ["🥓", "🌶️"]
},
{
"pizzaName": "Margherita",
"additionalToppings": ["🍌", "🌶️", "🍍"]
}
]
It can be recreated with the following script:
create table test(id serial primary key, json_data json);
insert into test(json_data) values (
'{
"id": 778,
"shop": "Luigis Pizza",
"name": "Edward Olson",
"phoneNumbers":
["(935)503-3765x4154","(935)12345"],
"address": "Unit 9398 Box 2056 DPO AP 24022",
"image": null,
"pizzas": [
{
"pizzaName": "Salami",
"additionalToppings": ["🥓", "🌶️"]
},
{
"pizzaName": "Margherita",
"additionalToppings": ["🍌", "🌶️", "🍍"]
}
]
}');
Tabulate a JSON document with JSON_TABLE function
To retrieve one or more JSON documents as columns and rows you can use the JSON_TABLE function. To retrieve the list of pizzas and their first additional topping from the above as table you can:
select tbl.* from
test,
JSON_TABLE(
json_data,
'$.pizzas[*]'
COLUMNS (
pizzaName VARCHAR(100) PATH '$.pizzaName',
additionalToppings VARCHAR(100) PATH '$.additionalToppings[0]'
)
) tbl;
Where
-
json_datais the JSON column -
'$.pizzas[*]'generates a row for each pizza in thepizzasarray -
pizzaName VARCHAR(100) PATH '$.pizzaName'retrieves thepizzaNamefield -
additionalToppings VARCHAR(100) PATH '$.additionalToppings[0]'retrieves the first element ([0]) of theadditionalToppingsarray
Result
+------------+--------------------+
| pizzaName | additionalToppings |
+------------+--------------------+
| Salami | 🥓 |
| Margherita | 🍌 |
+------------+--------------------+
Review all the JSON MySQL use-cases listed in the main page
Top comments (0)