addIndex doesn't work with JSONB indexes

[ThisIsMissEm]

In PostgreSQL, there's a JSONB data type that you can use, allowing you to use PostgreSQL kind of like a document database. In order to make this efficient, you can tell PostgreSQL to create indexes on keys and nested keys in JSONB documents. This can be done with:

CREATE INDEX "idx_TABLE_on_KEYS" ON "TABLE" ((data::jsonb->'KEY1'->>'KEY2'))

Using db.addIndex, I'd have thought I could do the following:

await db.addIndex(
  'TABLE',
  'idx_TABLE_on_KEYS',
  `(data->'KEY1'->>'KEY2')`
);

To produce the same index as the above SQL. However, it turns out that db.addIndex automatically (and perhaps sensibly) attempts to escape the values you're passing, which means you get the following query being executed:

CREATE INDEX "idx_TABLE_on_KEYS" ON "TABLE" ("(data->'KEY1'->>'KEY2')")

The additional quotes in this case actually break the creation of the index.

> [ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: column "(data->'KEY1'->>'KEY2')" does not exist

I'm not sure what the right fix is, but perhaps it'd be an idea to allow the user to say "actually, I know what I'm doing, please don't escape this" whilst giving the default of escaping column names.

Thoughts?

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[ThisIsMissEm]

Aside: I've ended up extracting the columns out of JSONB that I needed to index on, though I'll leave this open in case you want to add this as a feature.