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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
