Date: Mon, 28 Nov 2016 21:25:29 -0600
From: Nico Williams <nico@cryptonector.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Christian Convey <christian.convey@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	David Fetter <david@fetter.org>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Tackling JsonPath support
Message-ID: <20161129032526.GD24797@localhost>
References: <20681.1479057226@sss.pgh.pa.us>
 <CAFj8pRDvKiQ3YcNOCeEcOEy+OKh6LFcazmOe-Tit-gHyv1e+xQ@mail.gmail.com>
 <CAPfS4Zx8v1xx_OoG6n2_K2TthNudGshfkSbk=-__N+1-z8WSwg@mail.gmail.com>
 <20161128162639.GB27143@fetter.org>
 <CAFj8pRCSj4hQ_rOODyPp91asPdiLWLqTDcjJmxwjoaUfH1h_cQ@mail.gmail.com>
 <20161128192343.GD11117@localhost>
 <CAPfS4ZwgHT5FkRm8LE0ucG97e36ziBm0Pc-GkyGC15ksTwVFpw@mail.gmail.com>
 <20161129022623.GB24797@localhost>
 <CAPfS4ZyXtLgwZrZ46ec2S8RCdrjYq1iNh=qtM_g4hCcN-tSsmg@mail.gmail.com>
 <CAKFQuwZWHonT8=8F119f8PaDCkvWAyGu=z-gBDPULCDu_usR6w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKFQuwZWHonT8=8F119f8PaDCkvWAyGu=z-gBDPULCDu_usR6w@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Mon, Nov 28, 2016 at 08:00:46PM -0700, David G. Johnston wrote:
> IMO jq is considerably closer to XSLT than XPath - which leads me to figure
> that since xml has both that JSON can benefit from jq and json-path.  I'm
> not inclined to dig too deep here but I'd rather take jq in the form of
> "pl/jq" and have json-path (abstractly) as something that you can use like
> "pg_catalog.get_value(json, json-path)"

JSONPath looks a lot like a small subset of jq.  Here are some examples:

        JSONPath                    |                   jq
    -------------------------------------------------------------------

    $.store.book[0].title           | .store.book[0].title
    $['store']['book'][0]['title']  | .["store"]["book"][0]["title"]
    $..author                       | ..|.author
    $.store.*                       | .store[]
    $.store..price                  | .store|..|.price?
    $..book[2]                      | [..|.book?][2]
    $..book[?(@.isbn)]              | ..|.book?|select(.isbn)
    $..book[?(@.price<10)]          | ..|.book?|select(.price<10)
    $..*                            | ..?

Of course, jq can do much more than this.  E.g.,

    # Output [<title>, <price>] of all books with an ISBN:
    ..|.book?|select(.isbn)|[.title,.price]

    # Output the average price of books with ISBNs appearing anywhere in
    # the input document:
    reduce
      (..|.book?|select(.isbn)|.price) as $price
      (
       # Initial reduction state:
       {price:0,num:0};
       # State update
       .price = (.price * .num + $price) / (.num + 1) | .num += 1) |
    # Extract average price
    .price

Of course one could just wrap that with a function:

    def avg(pathexp; cond; v):
      reduce (pathexp | select(cond) | v) as $v
        ({v: 0, c: 0};
         .v = (.v * .c + $v) / (.c + 1) | .c += 1) | v;

    # Average price of books with ISBNs:
    avg(..|.book?; .isbn; .price)

    # Average price of all books:
    avg(..|.book?; true; .price)

There's much, much more.

Note that jq comes with a C implementation.  It should be easy to make
bindings to it from other programming language run-times.

Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers