可扩展属性场景下商品表结构重构咨询：如何实现基于JSON字段batch_property的查询

Hey there! Let's break down how to refactor your commodity table to make that JSON batch_property field queryable effectively. I've worked through similar scenarios a bunch of times, so here are the most practical approaches based on your database and use case:

Most modern databases (MySQL 5.7+, PostgreSQL 9.4+, SQL Server 2016+) have built-in JSON types and query functions—this is the fastest way to get queryable JSON without a full overhaul.

• First, alter your table to change batch_property from VARCHAR to the database's native JSON type:
  • MySQL: ALTER TABLE commodity_table MODIFY COLUMN batch_property JSON;
  • PostgreSQL: ALTER TABLE commodity_table MODIFY COLUMN batch_property jsonb; (jsonb is preferred here because it supports indexing and faster lookups than plain json)
• Create targeted indexes based on your common query patterns:
  • For a specific property (e.g., a_property) in MySQL: CREATE INDEX idx_commodity_a_prop ON commodity_table ((batch_property->>'$.a_property'));
  • For PostgreSQL jsonb, a general GIN index works for multiple properties: CREATE INDEX idx_commodity_batch_props ON commodity_table USING GIN (batch_property); or a single-property index: CREATE INDEX idx_commodity_a_prop ON commodity_table ((batch_property->>'a_property'));
• Example query:
  • MySQL: SELECT * FROM commodity_table WHERE batch_property->>'$.a_property' = 'a';
  • PostgreSQL: SELECT * FROM commodity_table WHERE batch_property @> '{"a_property": "a"}'::jsonb;
• Pros: No major structural changes, flexible for dynamic properties, easy to implement.
• Cons: Query performance is slightly slower than regular column queries, and may struggle with extremely large datasets (10M+ rows) or complex filter logic.

If certain JSON properties are constantly used for filtering, sorting, or joining (e.g., a_property, b_property), moving them to dedicated columns is the best bet for raw performance.

• Refactor your table structure by adding columns for these high-priority properties:

  ALTER TABLE commodity_table 
  ADD COLUMN a_property VARCHAR(255),
  ADD COLUMN b_property VARCHAR(255);
  

• Sync your data:
  • For new records, write values to both the dedicated columns and batch_property (or keep only dedicated columns and store remaining properties in batch_property).
  • For historical data, run a batch update: UPDATE commodity_table SET a_property = batch_property->>'$.a_property', b_property = batch_property->>'$.b_property';
• Add standard indexes to the new columns: CREATE INDEX idx_commodity_a_prop ON commodity_table (a_property);
• Example query: SELECT * FROM commodity_table WHERE a_property = 'a';
• Pros: Blazing-fast query performance (same as regular relational tables), supports complex filters, sorts, and joins.
• Cons: Poor scalability for new properties—you'll have to add more columns and update code every time a new queryable property comes up. Can lead to very wide tables if you have dozens of such properties.

If your batch properties are highly dynamic (lots of new properties added regularly, or properties vary wildly between commodities), an EAV model is designed for this kind of unstructured data.

• Create a separate table to store individual property key-value pairs:

  CREATE TABLE commodity_batch_property (
      id BIGINT PRIMARY KEY AUTO_INCREMENT,
      commodity_id BIGINT NOT NULL,
      property_key VARCHAR(100) NOT NULL,
      property_value VARCHAR(255) NOT NULL,
      FOREIGN KEY (commodity_id) REFERENCES commodity_table(commodityId)
  );
  

• Migrate existing data by splitting the JSON into rows: You'll need a script to parse each batch_property JSON and insert one row per key-value pair into the new table.
• Create a composite index for efficient lookups: CREATE INDEX idx_commodity_prop ON commodity_batch_property (commodity_id, property_key, property_value);
• Example query to find commodities with a_property = 'a':

  SELECT c.*
  FROM commodity_table c
  JOIN commodity_batch_property p ON c.commodityId = p.commodity_id
  WHERE p.property_key = 'a_property' AND p.property_value = 'a';
  

• Pros: Fully dynamic—add new properties without altering tables. Perfect for scenarios where properties are unpredictable or change often.
• Cons: Queries get complex quickly (especially with multiple filters), aggregation performance is poor, and data redundancy increases. Avoid this if you have frequent high-volume queries.

In most real-world projects, a mix of the above works best:

• Split high-frequency, stable properties into dedicated columns (for speed).
• Store low-frequency, dynamic properties in a native JSON field (for flexibility).
• Reserve the EAV model only for extreme cases where properties are extremely volatile.

This way you get the best of both worlds—fast queries for your most common use cases, and adaptability for edge cases.

内容的提问来源于stack exchange，提问作者aurora