JSON Support by Table Type

Support scope for JSON columns in each table type.

Support Summary

LOOKUP Tables

LOOKUP supports JSON as an ordinary column.

CREATE LOOKUP TABLE config_lookup (
    key    VARCHAR(64) PRIMARY KEY,
    site   VARCHAR(32),
    config JSON
);

INSERT INTO config_lookup VALUES (
    'device-001',
    'SEOUL',
    '{"region":"kr","level":3,"state":"ready"}'
);

SELECT key
FROM config_lookup
WHERE config->'$.region' = 'kr'
  AND JSON_EXTRACT_INTEGER(config, '$.level') >= 3;

Update JSON columns with JSON_SET, JSON_SET_JSON, JSON_REMOVE, and other JSON functions.

UPDATE config_lookup
SET config = JSON_SET(config, '$.state', 'active')
WHERE site = 'SEOUL';

JSON columns cannot be declared as primary keys.

-- Error
CREATE LOOKUP TABLE invalid_lookup (
    config JSON PRIMARY KEY
);

VOLATILE Tables

VOLATILE does not support JSON column creation.

CREATE VOLATILE TABLE session_data (
    session_id VARCHAR(64) PRIMARY KEY,
    payload    JSON
);

JSON Functions by Table Type

Usage Notes

• Write JSON paths as single-quoted strings, such as ‘$.key’.
• Use typed functions such as JSON_EXTRACT_INTEGER or JSON_EXTRACT_DOUBLE for numeric comparisons.
• LOOKUP does not support dedicated JSON path indexes. Extract frequently searched values into separate columns.