41 ms·

Whilst this looks interesting (particularly to anyone running PostgreSQL 11 or earlier) it's worth noting that PostgreSQL has support for SQL/JSON Path [1] as o

Whilst this looks interesting (particularly to anyone running PostgreSQL 11 or earlier) it's worth noting that PostgreSQL has support for SQL/JSON Path [1] as of version 12 and provides similar functionality without the use of an extension. [1] https://www.postgresql.org/docs/14/functions-json.html https://www.postgresql.org/docs/14/functions-json.html

eyelidlessness 5y ago
One thing the extension provides is two specific GIN indexes to optimize querying arbitrary JSON. Do you know if SQL/JSON Path optimizes similarly without the need for that sort of index? Last I checked (pre Postgres 12), JSONB indexes weren’t that general and require upfront knowledge of the structure you intend to query.
- laurencerowe 5y ago
  SQL/JSON path expressions use the built in jsonb_path_ops GIN index when you query using the operators (@@, @?). (They can also use the default jsonb GIN index but the jsonb_path_ops one is smaller and faster for most queries.) What do you mean by needing upfront knowledge of the structure you intend to query?
  - eyelidlessness 5y ago
    From https://www.postgresql.org/docs/11/datatype-json.html#JSON-INDEXING 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!
    - laurencerowe 5y ago
      The GIN index is general, the limitation is that the optimizer can only use the index for expressions where the operator is applied directly to the indexed field. The example in the docs is somewhat unfortunate because it can also be expressed directly on the indexed column using the jsonb containment operator, allowing the index can be used: -- Find documents in which the key "tags" contains key or array element "qui" SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; SQL/JSON path provides much greater expressivity than the containment operator so it allows more complex queries than can be expressed in terms of jsonb containment to be written directly on the indexed column enabling the index to be used. > 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. Definitely check with jsonb_path_ops too. While it doesn't cover quite as much as jsonb_ops the index is much more compact so queries are often quite a bit faster using it instead of the larger jsonb_ops index.

eyelidlessness 5y ago

One thing the extension provides is two specific GIN indexes to optimize querying arbitrary JSON. Do you know if SQL/JSON Path optimizes similarly without the need for that sort of index? Last I checked (pre Postgres 12), JSONB indexes weren’t that general and require upfront knowledge of the structure you intend to query.

laurencerowe 5y ago
SQL/JSON path expressions use the built in jsonb_path_ops GIN index when you query using the operators (@@, @?). (They can also use the default jsonb GIN index but the jsonb_path_ops one is smaller and faster for most queries.) What do you mean by needing upfront knowledge of the structure you intend to query?
- eyelidlessness 5y ago
  From https://www.postgresql.org/docs/11/datatype-json.html#JSON-INDEXING 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!
  - laurencerowe 5y ago
    The GIN index is general, the limitation is that the optimizer can only use the index for expressions where the operator is applied directly to the indexed field. The example in the docs is somewhat unfortunate because it can also be expressed directly on the indexed column using the jsonb containment operator, allowing the index can be used: -- Find documents in which the key "tags" contains key or array element "qui" SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; SQL/JSON path provides much greater expressivity than the containment operator so it allows more complex queries than can be expressed in terms of jsonb containment to be written directly on the indexed column enabling the index to be used. > 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. Definitely check with jsonb_path_ops too. While it doesn't cover quite as much as jsonb_ops the index is much more compact so queries are often quite a bit faster using it instead of the larger jsonb_ops index.