DEV Community

Francesco Tisiot
Francesco Tisiot

Posted on β€’ Originally published at ftisiot.net

1

How to tabulate a JSON document in 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

πŸ‘‰ 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

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

Sentry image

See why 4M developers consider Sentry, β€œnot bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

πŸ‘‹ Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay