Wildcard and JSON columns in Lindorm SQL

Scenario

Consider a user configuration management system that stores various settings per user. Because different users may have different configuration items, a wildcard JSON column provides the flexibility to handle varying schemas.

1. Create a table

-- Create a user configuration table
-- pk: User ID
-- username: Username
-- created_at: Creation time
-- `config_*`: A wildcard column of the JSON type, used to store various configurations
CREATE TABLE user_config (
    user_id VARCHAR,
    username VARCHAR,
    created_at TIMESTAMP,
    `config_*` JSON,
    PRIMARY KEY(user_id)
) WITH (
    wildcard_column='config_*',
    CONSISTENCY='strong',
    MUTABILITY='MUTABLE_LATEST'
);

-- View the table structure
DESCRIBE user_config;

-- View the CREATE TABLE statement
SHOW CREATE TABLE user_config;

2. Insert data

-- Insert configuration for user 1
UPSERT INTO user_config(
    user_id, 
    username, 
    created_at,
    config_profile,
    config_preference
) VALUES (
    'user001',
    'alice',
    CURRENT_TIMESTAMP,
    '{"avatar": "https://example.com/avatar1.jpg", "bio": "Software Engineer", "age": 28}',
    '{"theme": "dark", "language": "zh-CN", "notifications": true}'
);

-- Insert configuration for user 2 (includes more configuration items)
UPSERT INTO user_config(
    user_id,
    username,
    created_at,
    config_profile,
    config_preference,
    config_privacy
) VALUES (
    'user002',
    'bob',
    CURRENT_TIMESTAMP,
    '{"avatar": "https://example.com/avatar2.jpg", "bio": "Data Scientist", "age": 32, "location": "Beijing"}',
    '{"theme": "light", "language": "en-US", "notifications": false}',
    '{"profile_visible": true, "email_visible": false, "allow_messages": true}'
);

-- Insert configuration for user 3 (includes nested JSON)
UPSERT INTO user_config(
    user_id,
    username,
    created_at,
    config_profile,
    config_settings
) VALUES (
    'user003',
    'charlie',
    CURRENT_TIMESTAMP,
    '{"avatar": "https://example.com/avatar3.jpg", "bio": "Product Manager", "age": 35}',
    '{"display": {"font_size": 14, "line_height": 1.5}, "editor": {"auto_save": true, "tab_size": 4}}'
);

-- Batch insert configurations for multiple users
UPSERT INTO user_config(user_id, username, created_at, config_profile, config_preference) VALUES
    ('user004', 'david', CURRENT_TIMESTAMP, '{"avatar": "https://example.com/avatar4.jpg", "age": 25}', '{"theme": "dark", "language": "zh-CN"}'),
    ('user005', 'emma', CURRENT_TIMESTAMP, '{"avatar": "https://example.com/avatar5.jpg", "age": 29}', '{"theme": "light", "language": "en-US"}'),
    ('user006', 'frank', CURRENT_TIMESTAMP, '{"avatar": "https://example.com/avatar6.jpg", "age": 31}', '{"theme": "dark", "language": "ja-JP"}');

3. Basic queries

-- Query all user configurations
SELECT * FROM user_config LIMIT 100;

-- Query specific columns
SELECT user_id, username, config_profile, config_preference 
FROM user_config;

-- Use the -> operator to extract a JSON value
SELECT 
    user_id,
    username,
    config_profile->'$.age' AS age,
    config_profile->'$.bio' AS bio
FROM user_config;

-- Use the ->> operator to extract a JSON value as text
SELECT 
    user_id,
    username,
    config_profile->>'$.age' AS age,
    config_profile->>'$.bio' AS bio,
    config_preference->>'$.theme' AS theme
FROM user_config;

-- Use json_extract_string to extract a string value
SELECT 
    user_id,
    username,
    json_extract_string(config_profile, '$.bio') AS bio,
    json_extract_string(config_preference, '$.theme') AS theme
FROM user_config;

-- Use json_extract_long to extract a numeric value
SELECT 
    user_id,
    username,
    json_extract_long(config_profile, '$.age') AS age
FROM user_config;

4. Create secondary indexes

By default, the Lindorm wide table engine only supports efficient queries based on the primary key. To query a field inside a JSON column, you must create an index. Without one, the query triggers a full table scan and is rejected by the system. Creating secondary indexes on frequently queried fields improves performance and avoids full table scans.

-- Create an index on the bio field in config_profile
CREATE INDEX idx_profile_bio 
ON user_config (json_extract_string(config_profile, '$.bio'));

-- Create an index on the theme field in config_preference
CREATE INDEX idx_preference_theme 
ON user_config (json_extract_string(config_preference, '$.theme'));

-- Create an index on the language field in config_preference
CREATE INDEX idx_preference_language 
ON user_config (json_extract_string(config_preference, '$.language')) ;

-- Create an index with covering columns to avoid table lookups
CREATE INDEX idx_profile_age_with_cover 
ON user_config (json_extract_long(config_profile, '$.age')) 
INCLUDE(username, config_profile, config_preference);

-- View all indexes for the table
SHOW INDEX FROM user_config;

-- View detailed index information
SHOW TABLE VARIABLES FROM user_config;

5. Querying with a secondary index

-- Query for users with an age of 30 (uses an index)
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') = 30;

-- Query for users older than 30 (uses an index)
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;

-- Query for users who use the 'dark' theme (uses an index)
SELECT user_id, username, config_preference->>'$.theme' AS theme
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';

-- Query for users who are older than 25 and use the 'dark' theme
SELECT 
    user_id, 
    username,
    json_extract_long(config_profile, '$.age') AS age,
    json_extract_string(config_preference, '$.theme') AS theme
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 25
  AND json_extract_string(config_preference, '$.theme') = 'dark';

-- Query for users with an age between 25 and 35
SELECT 
    user_id, 
    username,
    json_extract_long(config_profile, '$.age') AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') BETWEEN 25 AND 35;

-- Use EXPLAIN to view the execution plan
EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;
-- The output confirms index usage, for example: "Chose index table [idx_profile_age]"

EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';
-- The output confirms index usage, for example: "Chose index table [idx_preference_theme]"

6. Update data

-- Update a user's profile configuration
UPSERT INTO user_config(user_id, config_profile)
VALUES (
    'user001',
    '{"avatar": "https://example.com/new_avatar1.jpg", "bio": "Senior Software Engineer", "age": 29, "skills": ["Java", "Python", "SQL"]}'
);

-- Update a user's preference configuration
UPSERT INTO user_config(user_id, config_preference)
VALUES (
    'user002',
    '{"theme": "auto", "language": "zh-CN", "notifications": true, "email_digest": "daily"}'
);

-- Add a new configuration item for a user
UPSERT INTO user_config(user_id, config_security)
VALUES (
    'user001',
    '{"two_factor_enabled": true, "login_alerts": true, "trusted_devices": ["device1", "device2"]}'
);

-- Update multiple configuration columns simultaneously
UPSERT INTO user_config(
    user_id,
    config_profile,
    config_preference,
    config_security
) VALUES (
    'user003',
    '{"avatar": "https://example.com/avatar3_new.jpg", "bio": "Senior Product Manager", "age": 36}',
    '{"theme": "dark", "language": "zh-CN", "notifications": true}',
    '{"two_factor_enabled": false, "login_alerts": false}'
);

7. Search indexes

-- Scenario 1: Create an index on config_*. 
-- The data types of sub-fields are automatically inferred from the inserted data. 
-- Only basic types such as long and keyword can be inferred.
CREATE INDEX idx_search USING SEARCH ON user_config(
  `config_*`
);

-- Scenario 2: Create an index on config_* and explicitly specify the data type for a sub-field. 
-- For example, you can define the 'address' sub-field as the text type for all columns matching the `config_*` pattern.
CREATE INDEX idx_search USING SEARCH ON user_config(
  `config_*`(mapping='{
      "type":"object",
      "properties": {
          "address": {
            "type": "text",
            "analyzer": "ik_max_word"
          }
      }
  }')
);

-- Query for users with an age of 30 (uses a search index)
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') = 30;

-- Query for users older than 30 (uses a search index)
SELECT user_id, username, config_profile->>'$.age' AS age
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;

-- Query for users who use the 'dark' theme (uses a search index)
SELECT user_id, username, config_preference->>'$.theme' AS theme
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';

-- Use EXPLAIN to view the execution plan
EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_long(config_profile, '$.age') > 30;
-- The output confirms index usage, for example: "Chose search index [idx_search]"

EXPLAIN SELECT user_id, username
FROM user_config
WHERE json_extract_string(config_preference, '$.theme') = 'dark';
-- The output confirms index usage, for example: "Chose search index [idx_search]"