5 ms·

Jsonpath – a query language for JSON in Postgres [pdf]

107 points by craigkerstiens 7y ago

stonogo 7y ago
Interesting to see the old Soviet Union TLD is still in use. There's a pretty nice jsonpath implementation that was recently produced at https://github.com/pacifica/python-jsonpath2 https://github.com/pacifica/python-jsonpath2 I haven't switched to it yet, but I'm keeping an eye on it, to see how long it's supported
- aasasd 7y ago
  > Interesting to see the old Soviet Union TLD is still in use. Weird to see a proper institution on that domain. The .su itself is quite alive and available for registration. It's mostly used as a “quirky knockoff tld,” in the spirit of .io and such.
- UncleEntity 7y ago
  Pimping my Boost::property_tree python bindings out today it seems... https://github.com/eponymous/python3-property_tree https://github.com/eponymous/python3-property_tree Mostly inspired by jsonpath -- IIRC I implemented as much of it which made sense like dotted tree lookup "tree.foo.bar", lambda searching (which I was tempted to name "where"(e.g. "for key, value in tree.foo.bar.where(lambda k,v: k > 100)") but thought better of it), equality and comparison operators and a few other things I can't quite remember off the top of my head. Doesn't do expression parsing (edit: actually...it does a limited form of expression parsing inherited from the C++ lib) but works for the couple simple projects I needed it for.
cle 7y ago
Here's the real JSONPath: https://goessner.net/articles/JsonPath/ https://goessner.net/articles/JsonPath/ This submission is for PostgreSQL's "JSON Path", which is very similar to the original JSONPath. I'm surprised, and disappointed, that there is no mention of the original JSONPath, which this is obviously inspired by.
- aasasd 7y ago
  So, what I'm seeing is that I currently have JQ, and if I get around to dealing with Postgre, I might also have SQL's JSONPath. And then if I get tired of juggling objects/dicts/arrays in my programs, I can also pick a not-quite-the-same JSONPath as a lib. (Though the latter is unlikely, since Lisp and FP have taught me that a bunch of functions feeding into each other will do exactly the same thing quite fine.)
  - neilv 7y ago
    Oleg Kiselyov's SXPath work shows XPath as combinators, in Scheme. John Clements's packaging of SXPath for Racket might be the most accessible way to first look at it: https://docs.racket-lang.org/sxml/sxpath.html https://docs.racket-lang.org/sxml/sxpath.html Then Kiselyov's original writings: http://okmij.org/ftp/Scheme/xml.html#SXPath http://okmij.org/ftp/Scheme/xml.html#SXPath Before Web browsers had nice developer tools in them, I made a little aid for crafting SXPath queries, for HTML scraping: https://www.neilvandyke.org/racket/webscraperhelper/ https://www.neilvandyke.org/racket/webscraperhelper/
  - Terretta 7y ago
    Don’t forget JMESPath, as built into AWS CLI: http://jmespath.org/ http://jmespath.org/ With language libraries or as jp CLI: http://jmespath.org/libraries.html http://jmespath.org/libraries.html Example AWS CLI tricks: https://gist.github.com/magnetikonline/6a382a4c4412bbb68e33e137b9a74168 https://gist.github.com/magnetikonline/6a382a4c4412bbb68e33e... https://opensourceconnections.com/blog/2015/07/27/advanced-aws-cli-jmespath-query/ https://opensourceconnections.com/blog/2015/07/27/advanced-a...
    - kapilvt 7y ago
      Jmespath is also built in to the azure cli
- theamk 7y ago
  Well, they say that they did not invent it, they have just implemented it as defined in ISO/IEC 9075:2016 standard [0] It is entirely possible that ISO design is based of Goessner’s work, but unfortunately it is just history at this point. Unfortunately this is what standardization often does - the work becomes attributed to organization, and individual authors are forgotten. [0] https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016 https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
hyuuu 7y ago
another alternative is jsonata, backed by IBM: https://github.com/jsonata-js/jsonata https://github.com/jsonata-js/jsonata
Kip9000 7y ago
Looks like an inferior subset of XPath which is much cleaner and powerful
- klodolph 7y ago
  Isn't XPath for XML? How would you use it with JSON?
  - thechao 7y ago
    Xpath’s relationship to XML is like CSS to HTML: its natural domain—and most common use—is XML. However, it is a general path selection/query mechanism with a UNIX-file-path-like syntax.
    - theamk 7y ago
      There are large parts of XPath which are XML specific, like attributes or namespaces or node names (JSON’s arrays of arrays have neither). I am sure with enough effort one could use Xpath for generic JSON queries, but this will require non-obvious redesign of the language.
picod 7y ago
my 110 loc implementation https://github.com/ldarren/pico-common/blob/master/src/plugins/json.js https://github.com/ldarren/pico-common/blob/master/src/plugi... you can try it here http://json.jasaws.com/ http://json.jasaws.com/
donpdonp 7y ago
json processing languages peaked with jq's language. https://stedolan.github.io/jq/manual/ https://stedolan.github.io/jq/manual/
- asah 7y ago
  Is there a detailed comparison between JSONpath and jq? I'm esp interested in examples where jq is more powerful, e.g. jq one liner but JSONpath can't.
Emily1636 7y ago
Cute dog doing exercise like humans http://bit.ly/2Jq3rz1 http://bit.ly/2Jq3rz1 Funny gorilla acting like humans see his video on link below http://bit.ly/2JmTNxr http://bit.ly/2JmTNxr Best way to get rid from dark spots on face just follow this https://zoomtips.blogspot.com/2019/05/Dark-spots.html https://zoomtips.blogspot.com/2019/05/Dark-spots.html Funny panda is playing with friends they are sho cute http://bit.ly/2JvDQF4 http://bit.ly/2JvDQF4
d33 7y ago
Does anybody else feel that the syntax is terrible and SQL is not a good fit for this? I, for one, would appreciate if the paths were accessible via a change to the SQL syntax as opposed to a magic operator or - even worse - a new function. Not to mention that the more complex the queries become, the more pain one can expect the query planner to be... personally, I'd prefer to make my own query plans instead, laying down the algorithms according to the best of my knowledge of the dataset. It's not like optimizing in C, where the compiler looks at the AST and can actually discover something you wouldn't think of. My experience with PostgreSQL's query planner is that it requires so much fine-tuning it's near unusable and super quirky. That isn't to say that I don't like PostgreSQL and its JSON capabilities - it's just that the more I use it, the more I wish there was something between it and current NoSQL databases...

Interesting to see the old Soviet Union TLD is still in use. There's a pretty nice jsonpath implementation that was recently produced at https://github.com/pacifica/python-jsonpath2 https://github.com/pacifica/python-jsonpath2 I haven't switched to it yet, but I'm keeping an eye on it, to see how long it's supported

aasasd 7y ago
> Interesting to see the old Soviet Union TLD is still in use. Weird to see a proper institution on that domain. The .su itself is quite alive and available for registration. It's mostly used as a “quirky knockoff tld,” in the spirit of .io and such.
UncleEntity 7y ago
Pimping my Boost::property_tree python bindings out today it seems... https://github.com/eponymous/python3-property_tree https://github.com/eponymous/python3-property_tree Mostly inspired by jsonpath -- IIRC I implemented as much of it which made sense like dotted tree lookup "tree.foo.bar", lambda searching (which I was tempted to name "where"(e.g. "for key, value in tree.foo.bar.where(lambda k,v: k > 100)") but thought better of it), equality and comparison operators and a few other things I can't quite remember off the top of my head. Doesn't do expression parsing (edit: actually...it does a limited form of expression parsing inherited from the C++ lib) but works for the couple simple projects I needed it for.

cle 7y ago

Here's the real JSONPath: https://goessner.net/articles/JsonPath/ https://goessner.net/articles/JsonPath/ This submission is for PostgreSQL's "JSON Path", which is very similar to the original JSONPath. I'm surprised, and disappointed, that there is no mention of the original JSONPath, which this is obviously inspired by.

aasasd 7y ago
So, what I'm seeing is that I currently have JQ, and if I get around to dealing with Postgre, I might also have SQL's JSONPath. And then if I get tired of juggling objects/dicts/arrays in my programs, I can also pick a not-quite-the-same JSONPath as a lib. (Though the latter is unlikely, since Lisp and FP have taught me that a bunch of functions feeding into each other will do exactly the same thing quite fine.)
- neilv 7y ago
  Oleg Kiselyov's SXPath work shows XPath as combinators, in Scheme. John Clements's packaging of SXPath for Racket might be the most accessible way to first look at it: https://docs.racket-lang.org/sxml/sxpath.html https://docs.racket-lang.org/sxml/sxpath.html Then Kiselyov's original writings: http://okmij.org/ftp/Scheme/xml.html#SXPath http://okmij.org/ftp/Scheme/xml.html#SXPath Before Web browsers had nice developer tools in them, I made a little aid for crafting SXPath queries, for HTML scraping: https://www.neilvandyke.org/racket/webscraperhelper/ https://www.neilvandyke.org/racket/webscraperhelper/
- Terretta 7y ago
  Don’t forget JMESPath, as built into AWS CLI: http://jmespath.org/ http://jmespath.org/ With language libraries or as jp CLI: http://jmespath.org/libraries.html http://jmespath.org/libraries.html Example AWS CLI tricks: https://gist.github.com/magnetikonline/6a382a4c4412bbb68e33e137b9a74168 https://gist.github.com/magnetikonline/6a382a4c4412bbb68e33e... https://opensourceconnections.com/blog/2015/07/27/advanced-aws-cli-jmespath-query/ https://opensourceconnections.com/blog/2015/07/27/advanced-a...
  - kapilvt 7y ago
    Jmespath is also built in to the azure cli
theamk 7y ago
Well, they say that they did not invent it, they have just implemented it as defined in ISO/IEC 9075:2016 standard [0] It is entirely possible that ISO design is based of Goessner’s work, but unfortunately it is just history at this point. Unfortunately this is what standardization often does - the work becomes attributed to organization, and individual authors are forgotten. [0] https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016 https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016

hyuuu 7y ago

another alternative is jsonata, backed by IBM: https://github.com/jsonata-js/jsonata https://github.com/jsonata-js/jsonata

Kip9000 7y ago

Looks like an inferior subset of XPath which is much cleaner and powerful

klodolph 7y ago
Isn't XPath for XML? How would you use it with JSON?
- thechao 7y ago
  Xpath’s relationship to XML is like CSS to HTML: its natural domain—and most common use—is XML. However, it is a general path selection/query mechanism with a UNIX-file-path-like syntax.
  - theamk 7y ago
    There are large parts of XPath which are XML specific, like attributes or namespaces or node names (JSON’s arrays of arrays have neither). I am sure with enough effort one could use Xpath for generic JSON queries, but this will require non-obvious redesign of the language.

picod 7y ago

my 110 loc implementation https://github.com/ldarren/pico-common/blob/master/src/plugins/json.js https://github.com/ldarren/pico-common/blob/master/src/plugi... you can try it here http://json.jasaws.com/ http://json.jasaws.com/

donpdonp 7y ago

json processing languages peaked with jq's language. https://stedolan.github.io/jq/manual/ https://stedolan.github.io/jq/manual/

asah 7y ago
Is there a detailed comparison between JSONpath and jq? I'm esp interested in examples where jq is more powerful, e.g. jq one liner but JSONpath can't.

Emily1636 7y ago

Cute dog doing exercise like humans http://bit.ly/2Jq3rz1 http://bit.ly/2Jq3rz1 Funny gorilla acting like humans see his video on link below http://bit.ly/2JmTNxr http://bit.ly/2JmTNxr Best way to get rid from dark spots on face just follow this https://zoomtips.blogspot.com/2019/05/Dark-spots.html https://zoomtips.blogspot.com/2019/05/Dark-spots.html Funny panda is playing with friends they are sho cute http://bit.ly/2JvDQF4 http://bit.ly/2JvDQF4

d33 7y ago

Does anybody else feel that the syntax is terrible and SQL is not a good fit for this? I, for one, would appreciate if the paths were accessible via a change to the SQL syntax as opposed to a magic operator or - even worse - a new function. Not to mention that the more complex the queries become, the more pain one can expect the query planner to be... personally, I'd prefer to make my own query plans instead, laying down the algorithms according to the best of my knowledge of the dataset. It's not like optimizing in C, where the compiler looks at the AST and can actually discover something you wouldn't think of. My experience with PostgreSQL's query planner is that it requires so much fine-tuning it's near unusable and super quirky. That isn't to say that I don't like PostgreSQL and its JSON capabilities - it's just that the more I use it, the more I wish there was something between it and current NoSQL databases...