Ygthkb

Disk space full during insert, what happens?

Crack the bank account's password!

Can a planet be tidally unlocked?

Is Developer Console going to be deprecated?

How can I keep my gold safe from other PCs?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?

What really causes series inductance of capacitors?

Why does a single AND gate need 60 transistors?

Why aren't passengers instructed how to lift aisle armrests?

Is there a way to pause a running process on Linux systems and resume later?

Missing a connection and don't have money to book next flight

Is the percentage symbol a constant?

Is layered encryption more secure than long passwords?

Sing Baby Shark

What is an explicit bijection in combinatorics?

Why don't you get burned by the wood benches in a sauna?

How do I purchase a drop bar bike that will be converted to flat bar?

How do I avoid the "chosen hero" feeling?

Coworker asking me to not bring cakes due to self control issue. What should I do?

What is the smallest molar volume?

Have any astronauts or cosmonauts died in space?

Is the tritone (A4 / d5) still banned in Roman Catholic music?

Why is it that Bernie Sanders always called a "socialist"?

Using GIN to index a JSON column

PostgreSQL GIN pg_trgm default operator classPostgreSQL index array of int4range using GIN / GIST - custom operator classWhy would you index text_pattern_ops on a text column?How to create an index on an integer json property in postgresoperator class “gin_trgm_ops” does not accept data type tsvectorPostgreSQL GIN pg_trgm default operator classJSON array format in MySQL not working?Working with JSON data MySQLPosgtreSQL GIN + BTree orderingHow to create pg_trgm compound indexes with date columnsHow to use default value of data type as column default?

1

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.

postgresql postgresql-9.4 json

share|improve this question

edited May 24 '17 at 10:16

Marco

3,73231624

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 1 more comment

1

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.

postgresql postgresql-9.4 json

share|improve this question

edited May 24 '17 at 10:16

Marco

3,73231624

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

 | 
show 1 more comment

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.

postgresql postgresql-9.4 json

share|improve this question

edited May 24 '17 at 10:16

Marco

3,73231624

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

share|improve this question

edited May 24 '17 at 10:16

Marco

3,73231624

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

share|improve this question

edited May 24 '17 at 10:16

Marco

3,73231624

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

edited May 24 '17 at 10:16

Marco

3,73231624

edited May 24 '17 at 10:16

Marco

3,73231624

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

asked May 24 '17 at 10:07

Munish DhimanMunish Dhiman

613

3 Answers
3

active

oldest

votes

5

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

share|improve this answer

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

add a comment | 

1

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.

share|improve this answer

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

0

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

share

answered 6 mins ago

K-GunK-Gun

1012

New contributor

K-Gun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f174411%2fusing-gin-to-index-a-json-column%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5

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

share|improve this answer

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

add a comment | 

5

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

share|improve this answer

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

add a comment | 

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

share|improve this answer

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

CREATE INDEX name ON table USING gin(column);

share|improve this answer

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

answered May 24 '17 at 11:46

Md Haidar Ali KhanMd Haidar Ali Khan

3,69462342

1

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.

share|improve this answer

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

1

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.

share|improve this answer

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

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.

share|improve this answer

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

ALTER TABLE api

  ALTER COLUMN jdoc

  SET DATA TYPE jsonb USING jdoc::jsonb;

share|improve this answer

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

answered Nov 27 '17 at 21:14

Evan CarrollEvan Carroll

32.5k970221

0

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

share

answered 6 mins ago

K-GunK-Gun

1012

New contributor

K-Gun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

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

share

answered 6 mins ago

K-GunK-Gun

1012

New contributor

K-Gun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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

share

answered 6 mins ago

K-GunK-Gun

1012

New contributor

K-Gun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

answered 6 mins ago

K-GunK-Gun

1012

New contributor

K-Gun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 6 mins ago

K-GunK-Gun

1012

New contributor

K-Gun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 6 mins ago

K-GunK-Gun

1012