[HN Gopher] Jsquery: A PostgreSQL extension for searching nested...
       ___________________________________________________________________
        
       Jsquery: A PostgreSQL extension for searching nested jsonb objects
       and arrays
        
       Author : eatonphil
       Score  : 59 points
       Date   : 2021-11-01 17:17 UTC (5 hours ago)
        
 (HTM) web link (github.com)
 (TXT) w3m dump (github.com)
        
       | JamesMcMinn wrote:
       | 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
        
         | eyelidlessness wrote:
         | 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 wrote:
           | 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 wrote:
             | 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!
        
       | fmajid wrote:
       | 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".
        
       | jfbaro wrote:
       | 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 wrote:
         | I recently found out that it comes with XML / xpath support by
         | default: https://twitter.com/tehwey/status/1453326284390600713
        
       ___________________________________________________________________
       (page generated 2021-11-01 23:01 UTC)