JSONLine 到 JSON 路径检索¶
先在 MatrixOne 服务端创建 JSONLine 文件,每行一个与表列同名的 JSON 对象:
printf '%s\n' '{"id":1,"payload":{"kind":"alarm","value":42}}' '{"id":2,"payload":{"kind":"info","value":7}}' > /tmp/mo-cookbook-events.jsonl
SET experimental_fulltext2_index = 1;
CREATE DATABASE cookbook_json;
CREATE TABLE cookbook_json.events(id BIGINT PRIMARY KEY, payload JSON);
LOAD DATA INFILE {'filepath'='/tmp/mo-cookbook-events.jsonl',
'format'='jsonline','jsondata'='object'}
INTO TABLE cookbook_json.events;
SELECT id, JSON_TYPE(JSON_EXTRACT(payload,'$.value')) AS value_type
FROM cookbook_json.events ORDER BY id;
CREATE FULLTEXT2 INDEX ft_payload ON cookbook_json.events(payload) WITH PARSER json;
SELECT id FROM cookbook_json.events
WHERE JSON_EXTRACT_STRING(payload,'$.kind') = 'alarm' ORDER BY id;
DROP DATABASE cookbook_json;
应加载两行;路径查询只返回 1。同时测试缺失路径和 JSON null,它们与 SQL NULL 的关系不能凭字符串展示推断。源码 JSON 基线 提供了类型、缺失路径和大值用例。