2

I need to add index on a JSON column in my table as per documentation I am executing a query similar to the one below.

CREATE INDEX idxgin ON api USING gin (jdoc);

I am getting following error:

ERROR: data type json has no default operator class for access method "gin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

Here is the link for the documentation.

  • You can see from the link that you have provided that only jsonb can be indexed with gin. And you are clearly trying to create an index on the column that is type of json. If you can't change column type you can have for examle btree index on some key in the json. Like so: CREATE INDEX ON api((column->>'id')); – ffox003 May 24 '17 at 10:17
  • @Munish Dhiman,The default GIN operator class for jsonb supports queries with the @>, ?, ?& and ?| operators. – Md Haidar Ali Khan May 24 '17 at 10:26
  • dba.stackexchange.com/questions/145586/… .. it is duplicate thread – Md Haidar Ali Khan May 24 '17 at 10:28
  • @ffox003 Yes you are right, I just mixed json and jsonb datatypes in my mind. Thanks :) – Munish Dhiman May 24 '17 at 11:02
  • @Munish Dhiman, what you executing the query? Could you mention. – Md Haidar Ali Khan May 24 '17 at 11:17
6

GiN(Generalized Inverted Index) is inverted index, a structure that has only one single index entry per a key and store the mapping information(posting list) of all key row to have the same value in the index entry. In postgreSQL, the key is stored in the index entry and mapping information for the key is stored in the posting tree. To search the index entry and posting tree is using a B-Tree. Therefore, GIN is useful when an index must map many values to on row, such as indexing array, documents.

I suppose to that before creating GIN index , you are doing some mistake. as you see here postgresql GIN pg_trgm default operator class

To Creates a GIN (Generalized Inverted Index) the syntax should be like that

CREATE INDEX name ON table USING gin(column);

Creates a GIN (Generalized Inverted Index)-based index. The column must be of tsvector type.

Default operator class jsonb_ops supports existence operators( ?, ?&, ?| ) and containment operator( @> ). And jsonb_path_ops supports containment operator only. Therefore GIN index is only possible to search for that have a particular key or key-values.

When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> oerator returns the value of the specified attribute in text format. PostgreSQL can use indexes for the text results as compare operands. GIN index can be used by GIN jsonb operator class.

To see the json operator Here

For further ref Here and Here

2

JSON is a json-validated text type: a Javascript object serialized into text. You can't usefully index that. You can only index data. In order to make that text data, we move it into a format that we makes sense.

  1. Converts the text to an JSON object.
  2. Stores that object using indexable Javascript primitives (binary).

That type in jsonb. So convert jdoc from JSON to JSONB,

ALTER TABLE api
  ALTER COLUMN jdoc
  SET DATA TYPE jsonb USING jdoc::jsonb;

Now your exact query above will work.

0

Seems this also works: CREATE INDEX idxgin ON api USING gin ((jdoc::jsonb));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.