Load JSONLine and query JSON paths¶
Create a JSONLine file on the MatrixOne server. Each line is a JSON object with keys matching the table columns:
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;
Two rows should load, and the path query should return only 1. Also check a missing path and JSON null; their relationship with SQL NULL cannot be inferred from their string display. The source JSON baseline covers types, missing paths, and large values.