JSON関数とJSONドット表記

json_column.key

-- JSON列から特定キーの値を抽出
SELECT data.temperature AS temp FROM sensor_log;

-- WHERE句で使用
SELECT * FROM sensor_log
 WHERE data.status = 'active';

JSON_SET(json_doc, path, scalar)

Mach> SELECT JSON_SET('{"ship":{"status":"READY"}}', '$.ship.status', 'DONE') FROM dual;
{"ship":{"status":"DONE"}}

Mach> SELECT JSON_SET('{"count":0}', '$.count', 42) FROM dual;
{"count":42}

JSON_SET_JSON(json_doc, path, json_text)

Mach> SELECT JSON_SET_JSON('{"ship":{}}', '$.ship.owner', '{"name":"machbase"}') FROM dual;
{"ship":{"owner":{"name":"machbase"}}}

Mach> SELECT JSON_SET_JSON('{"tags":{}}', '$.tags.sensors', '[1,2,3]') FROM dual;
{"tags":{"sensors":[1,2,3]}}

JSON_REMOVE(json_doc, path)

Mach> SELECT JSON_REMOVE('{"owner":{"name":"machbase","team":"db"}}', '$.owner.team') FROM dual;
{"owner":{"name":"machbase"}}

Mach> SELECT JSON_REMOVE('{"a":1,"b":2}', '$.a') FROM dual;
{"b":2}

-- JSON型の列を含むLOGテーブル
CREATE LOG TABLE device_log (
    ts    DATETIME,
    data  JSON
);

-- JSONデータの挿入
INSERT INTO device_log VALUES (NOW, '{"temperature":23.5,"humidity":60,"status":"active"}');

-- JSONドット表記で値を抽出
SELECT ts, data.temperature AS temp
  FROM device_log
 WHERE data.status = 'active';