Mysql JSON 基本操作

big_cat發表於2023-02-21
MySqlJSON

表結構

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `profile` json NOT NULL COMMENT "資料 object",
  `favor` json NOT NULL "收藏 array",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入資料

可以使用 JSON_OBJECT / JSON_ARRAY 來組裝 JSON 資料

INSERT INTO `users`(`profile`, `favor`) VALUES
(JSON_OBJECT("name", "big", "age", 18, "hobby", JSON_ARRAY("football", "game")), JSON_ARRAY(1, 2)),
(JSON_OBJECT("name", "cat", "age", 20, "hobby", JSON_ARRAY("basketball", "game")), JSON_ARRAY(2, 3)),
(JSON_OBJECT("name", "sqrt", "age", 24, "hobby", JSON_ARRAY("game")), JSON_ARRAY(2));

查詢資料

  1. key 使用 $.key1.key2 的方式讀取
  2. arr 使用 $[index] 的方式讀取
  3. {"key1": {"key2": [1, 2, 3]}} 讀取 $.key1.key2[0]

JSON_CONTAINS 即可以做等值查詢，也可以對陣列做元素包含查詢

SELECT
    `profile` -> "$.name" AS `name`,
    `profile` -> "$.hobby[0]" AS `hobby_first`,
    `profile` -> "$.ext.avatar" AS `avatar`,
    `favor` -> "$[0]" AS `favor_first` 
FROM
    `users` 
WHERE
    `profile` -> "$.age" >= 20
    AND JSON_CONTAINS(`profile`, '"big"', "$.name")
    AND JSON_CONTAINS(`profile`, '"game"', "$.hobby")
    AND JSON_CONTAINS(`favor`, 1, "$");

-- profile.age >= 20
-- profile.name = "big"
-- profile.hobby 中有 "game"
-- favor 中有 1
-- 沒有 "basketball" 愛好的使用者
SELECT
    `profile` 
FROM
    users 
WHERE
    !JSON_CONTAINS( `profile`, '"basketball"', "$.hobby" )

陣列操作

JSON_ARRAY: 建立 json array
JSON_ARRAY_INSERT: 向 json array 中插入資料 操作的物件 必須為陣列
JSON_ARRAY_APPEND: 如果操作物件不是陣列，則會轉為陣列並追加 / 是陣列則會直接追加

-- [1, 2, 3]
SELECT JSON_ARRAY(1, 2, 3); 

-- 指定插入的位序
SELECT JSON_ARRAY_INSERT("[1,2,3]", "$[0]", "hello1");
SELECT JSON_ARRAY_INSERT("[1,2,3]", "$[3]", "hello2");
-- 越界了會被追加在最後 [1, 2, 3, "hello100"]
SELECT JSON_ARRAY_INSERT("[1,2,3]", "$[100]", "hello100");

-- [1, [2, "hello3"], 3]
SELECT JSON_ARRAY_INSERT("[1,[2],3]", "$[1][1]", "hello3");

-- [1, 2, 3, "hello4"]
SELECT JSON_ARRAY_APPEND("[1,2,3]", "$", "hello4");
-- [[1, "hello5"], 2, 3]
SELECT JSON_ARRAY_APPEND("[1,2,3]", "$[0]", "hello5");

屬性操作

  1. JSON_INSERT 插入某屬性值（屬性不存在時才會插入）
  2. JSON_SET/JSON_REPLACE 設定某屬性值
  3. JSON_REMOVE 刪除某屬性值
-- profile.name 移除
UPDATE `users` SET `profile` = JSON_REMOVE(`profile`, "$.name");
-- profile.name 插入 （!!path 不存在時才會執行插入!!）
UPDATE `users` SET `profile` = JSON_INSERT(`profile`, "$.name", "sqrt");

-- favor 追加 a、b
UPDATE `users` SET `favor` = JSON_ARRAY_APPEND(`favor`, "$", "a", "$", "b");
UPDATE `users` SET `favor` = JSON_ARRAY_INSERT(`favor`, "$", "a", "$", "b");

-- favor 設為 xxx 值
UPDATE `users` SET `favor` = JSON_SET(`favor`, "$", JSON_ARRAY());

-- profile.name 設為 json_set / json_replace
UPDATE `users` SET `profile` = JSON_SET(`profile`, "$.name", "json_set");
UPDATE `users` SET `profile` = JSON_REPLACE(`profile`, "$.name", "json_replace");

相關文章