Как табулировать JSON-документ в MySQL

Как табулировать JSON-документ в MySQL

Комментарии
Содержание
  1. Набор данных
  2. Табуляция JSON-документа с помощью функции JSON_TABLE.

Вы можете табулировать JSON-документ (получить его в виде строки) в MySQL с помощью функции JSON_TABLE.

ПРИМЕЧАНИЕ: более подробную информацию можно найти на странице документации MySQL JSON functions documentation page

👉 Нужна БЕСПЛАТНАЯ база данных MySQL? Посмотрите Бесплатные планы Aiven👈

Набор данных

Набор данных следующий:

{
	"id": 778,
	"shop": "Luigis Pizza",
	"name": "Эдвард Олсон",
	"phoneNumbers": ["(935)503-3765x4154", "(935)12345"],
	"address": "Unit 9398 Box 2056 DPO AP 24022",
	"image": null,
	"pizzas": [
		{
			"pizzaName": "Салями",
			"additionalToppings": ["🥓", "🌶️"]
		},
		{
			"pizzaName": "Margherita",
			"additionalToppings": ["🍌", "🌶️", "🍍"]
		}
	]
}

В следующих примерах используется набор данных заказов пиццы, в котором есть заказ:

  • id: 778
  • магазин: “Luigis Pizza”
  • имя: ”Эдвард Олсон.
  • phoneNumbers:[”(935)503-3765x4154”,”(935)12345”]
  • Адрес: “Unit 9398 Box 2056 DPO AP 24022”
  • изображение: null
  • и две пиццы, содержащиеся в элементе pizzas:
[
	{
		"pizzaName": "Салями",
		"additionalToppings": ["🥓", "🌶️"]
	},
	{
		"pizzaName": "Margherita",
		"additionalToppings": ["🍌", "🌶️", "🍍"]
	}
]

Его можно воссоздать с помощью следующего скрипта:

создать таблицу test(id serial primary key, json_data json);

insert into test(json_data) values (
'{
    "id": 778,
    "shop": "Luigis Pizza",
    "name": "Эдвард Олсон",
    "phoneNumbers":
        ["(935)503-3765x4154","(935)12345"],
    "address": "Unit 9398 Box 2056 DPO AP 24022",
    "image": null,
    "pizzas": [
        {
            "pizzaName": "Салями",
            "additionalToppings": ["🥓", "🌶️"]
        },
        {
            "pizzaName": "Margherita",
            "additionalToppings": ["🍌", "🌶️", "🍍"]
        }
    ]
}');

Табуляция JSON-документа с помощью функции JSON_TABLE.

Чтобы получить один или несколько JSON-документов в виде столбцов и строк, можно воспользоваться функцией JSON_TABLE. Чтобы получить список пицц и их первых дополнительных начинок из приведенного выше документа в виде таблицы, вы можете:

select tbl.* from
    test,
    JSON_TABLE(
        json_data,
        '$.pizzas[*]'
        COLUMNS (
            pizzaName VARCHAR(100) PATH '$.pizzaName',
            additionalToppings VARCHAR(100) PATH '$.additionalToppings[0]'
            )
        ) tbl;

Где

  • json_data - столбец JSON
  • '$.pizzas[*]' генерирует строку для каждой пиццы в массиве pizzas.
  • pizzaName VARCHAR(100) PATH '$.pizzaName' извлекает поле pizzaName.
  • additionalToppings VARCHAR(100) PATH '$.additionalToppings[0]' извлекает первый элемент ([0]) массива additionalToppings.

Результат

+------------+--------------------+
| pizzaName | additionalToppings |
+------------+--------------------+
| Салями | 🥓 |
| Маргерита | 🍌 |
+------------+--------------------+

Просмотрите все примеры использования JSON MySQL, перечисленные на главной странице