PostgreSQL 9.3 中在 json 字段的嵌套属性上创建索引的多种方法

PostgreSQL 9.3 中在 json 字段的嵌套属性上创建索引的多种方法

json
{
  "name": "John",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY"
  }
}

sql
CREATE INDEX idx_json_data ON my_table USING gin (json_data);

sql
CREATE INDEX idx_json_address ON my_table USING gin (json_data->'address' jsonb_path_ops);

sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSONB
);
INSERT INTO users (profile)
VALUES ('{"name": "John", "age": 30, "address": {"street": "123 Main St", "city": "New York", "state": "NY"}}'),
       ('{"name": "Jane", "age": 25, "address": {"street": "456 Elm St", "city": "San Francisco", "state": "CA"}}'),
       ('{"name": "Bob", "age": 35, "address": {"street": "789 Oak St", "city": "Chicago", "state": "IL"}}');

sql
-- 索引整个 JSON 字段
CREATE INDEX idx_users_profile ON users USING gin (profile);
-- 索引嵌套属性
CREATE INDEX idx_users_address ON users USING gin (profile->'address' jsonb_path_ops);

sql
-- 查询整个 JSON 字段
SELECT * FROM users WHERE profile @> '{"name": "John"}';
-- 查询嵌套属性
SELECT * FROM users WHERE profile->'address' @> '{"city": "New York"}';