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:

    # Output the average price of books with ISBNs appearing anywhere in
    # the input document:
      (..|.book?|select(.isbn)|.price) as $price
       # Initial reduction state:
       # State update
       .price = (.price * .num + $price) / (.num + 1) | .num += 1) |
    # Extract average 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.


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

Reply via email to