Is a table with (id PRIMARY KEY, data JSON) a good index for searching by key in PostgreSQL?

An index on a PostgreSQL column with JSON (or JSONB) type can be effective for key-based searches, but it depends on the index type and data structure.

If you have a table with a data JSON column, for fast key searches within JSON, it is recommended to use the JSONB type and create a GIN index:

CREATE INDEX idx_data_gin ON table_name USING GIN (data jsonb_path_ops);

Or simply:

CREATE INDEX idx_data_gin ON table_name USING GIN (data);

This allows efficient queries with operators like @>, for example:

SELECT * FROM table_name WHERE data @> '{"key": "value"}';

If only JSON without indexing is used, searches will be slow because the entire table will be scanned.

Therefore, a regular index on id does not help for key searches within JSON. A specialized GIN index on JSONB is needed for that.