MySQL JSON 欄位操作#

可能使用到的 MySQL FUNCTION 或 特殊關鍵字:#

• JSON_VALUE: 取得 JSON 物件中的值
• JSON_OVERLAPS: 比對 JSON 物件中的值是否有交集(任一即可)
• JSON_CONTAINS: 比對 JSON 物件中的值是否有交集(全部必須符合)
• CAST: 強制轉型
• MEMBER OF: 判斷是否在陣列中
• ARRAY: 轉換成陣列

建立表，插入一些數據做為測試:#

payload 中的資料會有:

• path: array<string>: 代表路徑, 例如: ["/", "usr", "local", "bin"]
• name: string: 代表檔案名稱, 例如: nvm
• owner: string: 代表檔案擁有者, 例如: sam
• group: string: 代表檔案群組, 例如: root

CREATE TABLE file_sys (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO file_sys (payload) VALUES 
('{"path": ["/", "usr", "local", "bin"], "name": "nvm", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "gvm", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "code", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "docker", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "composer", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "docker-composer", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "orb", "owner": "sam", "group": "root"}'),
('{"path": ["/", "usr", "local", "bin"], "name": "vagrant", "owner": "sam", "group": "root"}'),
('{"path": ["/", "bin"], "name": "ls", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "bash", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "cat", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "dd", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "df", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "mkdir", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "pwd", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "sleep", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "sh", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "rmdir", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "sync", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "tcsh", "owner": "root", "group": "root"}'),
('{"path": ["/", "bin"], "name": "chmod", "owner": "root", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "bin"], "name": "php", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "bin"], "name": "mongosh", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "bin"], "name": "mongos", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "bin"], "name": "php-cs-fixer", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "lib", "php", "20230831"], "name": "opcache.so", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "lib", "php", "build"], "name": "php.m4", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "lib", "php", "build"], "name": "config.sub", "owner": "sam", "group": "root"}'),
('{"path": ["/", "opt", "homebrew", "lib", "php", "build"], "name": "libtool.m4", "owner": "sam", "group": "root"}'),
('{"name": "docker-composer", "path": ["/", "local", "usr", "bin"], "group": "root", "owner": "sam"}'),
('{"path": ["/", "opt", "homebrew", "lib", "php", "build"], "name": "phpize.m4", "owner": "sam", "group": "root"}');

單個鍵(字串)建立索引#

使用 payload->>'.$name' 建立索引#

-- JSON 中某個鍵值當作索引(這樣好像是建立虛擬索引)
CREATE INDEX idx_payload_name ON file_sys ((CAST(payload->>'$.name' AS CHAR(50))))
-- 這樣吃得到
SELECT * FROM file_sys WHERE CAST(payload->>'$.name' AS CHAR(50)) = 'nvm';
-- 這樣吃不到
SELECT * FROM file_sys WHERE JSON_VALUE(payload, '$.name') = 'nvm'
-- 這樣吃不到
SELECT * FROM file_sys WHERE payload->>'$.name' = 'nvm'

根據某個欄位建立 generated column，並加上 index#

ALTER TABLE file_sys ADD COLUMN file_name CHAR(50) GENERATED ALWAYS AS (CAST(payload->>'$.name' AS CHAR(50))) STORED;
CREATE INDEX idx_file_name ON file_sys(file_name);

多鍵索引 -> 將 path 裡面的一維陣列展開，轉化成 CHAR(50) ARRAY 索引#

CREATE INDEX idx_path ON file_sys ((cast(payload->>'$.path' AS CHAR(50) ARRAY)))

-- 使用 MEMBER OF 來查詢(in_array)
SELECT * FROM file_sys WHERE 'local' MEMBER OF (payload->>'$.path'); -- index: ref
SELECT * FROM file_sys WHERE JSON_OVERLAPS(payload->>'$.path', (cast('["local", "homebrew"]' AS JSON))); -- 任一符合就回傳(多元素) - invalid
SELECT * FROM file_sys WHERE JSON_OVERLAPS(payload->>'$.path', (cast('["local"]' AS JSON))); -- 任一符合就回傳(單元素) - index: range
SELECT * FROM file_sys WHERE JSON_CONTAINS(payload->>'$.path', (cast('["bin", "local"]' AS JSON))); -- 交集才回傳(多元素) - invalid
SELECT * FROM file_sys WHERE JSON_CONTAINS(payload->>'$.path', (cast('["local"]' AS JSON))); -- 交集才回傳(單元素) - index: range