DEV Community

Francesco Tisiot
Francesco Tisiot

Posted on • Originally published at ftisiot.net

How to tabulate a JSON document in MySQL

#mysql #json #tabulate

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": ["🍌", "🌶️", "🍍"] } ] }
Enter fullscreen mode Exit fullscreen mode

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 pizzas item: 
[ { "pizzaName": "Salami", "additionalToppings": ["🥓", "🌶️"] }, { "pizzaName": "Margherita", "additionalToppings": ["🍌", "🌶️", "🍍"] } ]
Enter fullscreen mode Exit fullscreen mode

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": ["🍌", "🌶️", "🍍"] } ] }');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Where

  • json_data is the JSON column
  • '$.pizzas[*]' generates a row for each pizza in the pizzas array
  • pizzaName VARCHAR(100) PATH '$.pizzaName' retrieves the pizzaName field
  • additionalToppings VARCHAR(100) PATH '$.additionalToppings[0]' retrieves the first element ([0]) of the additionalToppings array

Result

+------------+--------------------+ | pizzaName | additionalToppings | +------------+--------------------+ | Salami | 🥓 | | Margherita | 🍌 | +------------+--------------------+
Enter fullscreen mode Exit fullscreen mode

Review all the JSON MySQL use-cases listed in the main page

Top comments (0)