Menu
You can index JSON data as you would any data of the type you use to store it. In addition, you can define a JSON search index, which is useful for both ad hoc structural queries and full-text queries.
In MySQL, the only way to index a JSON path expression is to add a virtual column that mirrors the path expression in question and build an index on the virtual column. As you can see, the title column is mapped to the $. title path expression on the properties JSON column.
When we use ->> operator of JSONB, PostgreSQL can use B-tree or Hash index for processing the operations. ->> operator 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 the GIN JSONB operator class.
Querying JSON dataPostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field by key. The operator ->> returns JSON object field by text.
Found:
CREATE TABLE publishers(id INT, info JSON);
CREATE INDEX ON publishers((info->>'name'));
As stated in the comments, the subtle difference here is ->> instead of ->. The former one returns the value as text, the latter as a JSON object.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With