How to tabulate a JSON document in MySQL
Tags: mysql, json, tabulate
Published: 2023-10-09 11:00:00

---
title: How to tabulate a JSON document in MySQL
published: true
date: 2023-10-09 11:00 UTC
tags: mysql, json, tabulate
canonical_url: https://ftisiot.net/mysqljson/how-to-tabulate-JSON-document-mysql/
---

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](https://dev.mysql.com/doc/refman/8.0/en/json.html)

<p style="background: #cccccc;border: 1px solid #666666;border-radius: 15px;text-align: center;">👉 Need a <b>FREE</b> MySQL database? check <a href="https://go.aiven.io/francesco-signup">Aiven's free plans</a>👈</p>


## 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 `pizzas` item:

```
[
    {
        "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_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 | 🍌                 |
+------------+--------------------+
```


Review all the JSON MySQL use-cases listed in the [main page](https://ftisiot.net/mysqljson/main)