From: | Nico Williams <nico(at)cryptonector(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Christian Convey <christian(dot)convey(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, David Fetter <david(at)fetter(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Tackling JsonPath support |
Date: | 2016-11-29 03:25:29 |
Message-ID: | 20161129032526.GD24797@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
--
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2016-11-29 04:17:29 | Re: Proposal: scan key push down to heap [WIP] |
Previous Message | David G. Johnston | 2016-11-29 03:00:46 | Re: Tackling JsonPath support |