仅在postgresql中获取特定json标记的所有条目

CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO my_table (data) VALUES
    ('{"name": "John", "age": 30, "city": "New York"}'),
    ('{"name": "Jane", "age": 25, "city": "London"}'),
    ('{"name": "Bob", "age": 35, "city": "Paris"}');

SELECT * FROM my_table WHERE data->>'city' = 'New York';

CREATE INDEX idx_data_city ON my_table ((data->>'city'));

SELECT * FROM my_table WHERE data->>'city' = 'New York';