4 ms·

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

79 points by eatonphil 5y ago

jfbaro 5y ago
I am a PostgreSQL enthusiast (neither a developer nor a DBA, though), and I am always surprised by the number of capabilities PostgreSQL (and the community) offers. It's a true DATA ecosystem.
- dewey 5y ago
  I recently found out that it comes with XML / xpath support by default: https://twitter.com/tehwey/status/1453326284390600713 https://twitter.com/tehwey/status/1453326284390600713
JamesMcMinn 5y ago
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.
fmajid 5y ago
jsquery is absolutely amazing technology. A few years ago I prototyped a user path analysis DB where we replaced a 25GB time series database of user events with one where each user has one row with a JSONB array representing their entire history, and querying it using jsquery with GIN indexes took milliseconds. Things like "find me all the users who added item X to their cart and then went on to purchase item Y instead".
- xkcd-sucks 5y ago
  If you don't mind me asking, how large could each row get and was there a strict upper limit? In the past we've seen a massive performance cliff after a few individual rows get over some size (maybe 50Mb?)

79 points by eatonphil 5y ago

I am a PostgreSQL enthusiast (neither a developer nor a DBA, though), and I am always surprised by the number of capabilities PostgreSQL (and the community) offers. It's a true DATA ecosystem.

dewey 5y ago
I recently found out that it comes with XML / xpath support by default: https://twitter.com/tehwey/status/1453326284390600713 https://twitter.com/tehwey/status/1453326284390600713

JamesMcMinn 5y ago

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.

fmajid 5y ago

jsquery is absolutely amazing technology. A few years ago I prototyped a user path analysis DB where we replaced a 25GB time series database of user events with one where each user has one row with a JSONB array representing their entire history, and querying it using jsquery with GIN indexes took milliseconds. Things like "find me all the users who added item X to their cart and then went on to purchase item Y instead".

xkcd-sucks 5y ago
If you don't mind me asking, how large could each row get and was there a strict upper limit? In the past we've seen a massive performance cliff after a few individual rows get over some size (maybe 50Mb?)