Jsquery: A PostgreSQL extension for searching nested jsonb objects and arrays

From https://www.postgresql.org/docs/11/datatype-json.html#JSON-I...:

> We store these documents in a table named api, in a jsonb column named jdoc. If a GIN index is created on this column, queries like the following can make use of the index:

    -- Find documents in which the key "company" has value "Magnafone"
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

> However, the index could not be used for queries like the following, because though the operator ? is indexable, it is not applied directly to the indexed column jdoc:

    -- Find documents in which the key "tags" contains key or array element "qui"
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

> Still, with appropriate use of expression indexes, the above query can use an index. If querying for particular items within the "tags" key is common, defining an index like this may be worthwhile:

    CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

> Now, the WHERE clause jdoc -> 'tags' ? 'qui' will be recognized as an application of the indexable operator ? to the indexed expression jdoc -> 'tags'.

- - -

Taking a look at the most recent docs, it seems jsonb_ops should provide similar index optimization characteristics to the index operations in the extension for the native SQL/JSON Path type. Likely with similar trade offs. Good to know!