_______ __ _______
| | |.---.-..----.| |--..-----..----. | | |.-----..--.--.--..-----.
| || _ || __|| < | -__|| _| | || -__|| | | ||__ --|
|___|___||___._||____||__|__||_____||__| |__|____||_____||________||_____|
on Gopher (inofficial)
URI Visit Hacker News on the Web
COMMENT PAGE FOR:
URI Show HN: JSON Query
nothrabannosir wrote 9 hours 38 min ago:
crucial jq insight which unlocked the tool for me: it's jsonl, not
json.
it's a pipeline operating on a stream of independent json terms. The
filter is reapplied to every element from the stream. Streams !=
lists; the latter are just a data type. `.` always points at the
current element of the stream. Functions like `select` operate on
separate items of the stream, while `map` operates on individual
elements of a list. If you want a `map` over all elements of the
stream: that's just what jq is, naturally :)
stream of a single element which is a list:
echo '[1,2,3,4]' | jq .
# [1,2,3,4]
unpack the list into a stream of separate elements:
echo '[1,2,3,4]' | jq '.[]'
# 1
# 2
# 3
# 4
echo '[1,2,3,4]' | jq '.[] | .' # same: piping into `.` is a NOP:
only keep elements 2 and 4 from the stream, not from the array--there
is no array left after .[] :
echo '[1,2,3,4]' | jq '.[] | select(. % 2 == 0)'
# 2
# 4
keep the array:
echo '[1,2,3,4]' | jq 'map(. * 2)'
# [2,4,6,8]
map over individual elements of a stream instead:
echo '[1,2,3,4]' | jq '.[] | . * 2'
# 2
# 4
# 6
# 8
printf '1\n2\n3\n4\n' | jq '. * 2' # same
This is how you can do things like
printf '{"a":{"b":1}}\n{"a":{"b":2}}\n{"a":{"b":3}}\n' | jq
'select(.a.b % 2 == 0) | .a'
# {"b": 2}
select creates a nested "scope" for the current element in its parens,
but restores the outer scope when it exits.
Hope this helps someone else!
kunley wrote 11 hours 2 min ago:
Lot of people focus on a similarity to jq et al, I guess the author had
his reasons to craft own stuff.
Kudos for all the work, it's a nice language. I find writing parsers a
very mind-expanding activity.
ksclarke wrote 18 hours 52 min ago:
[1] -- jq is ubiquitous, but I still prefer the jsoniq syntax.
URI [1]: https://www.jsoniq.org
cweagans wrote 20 hours 30 min ago:
"JSON Query" is kind of a long name. You should find a way to shorten
it. Maybe "jQuery" or something along those lines :P
emadda wrote 22 hours 2 min ago:
Maybe JS directly?
gcr wrote 22 hours 40 min ago:
I read the man page of `jq` and learned how to use it. It's quite
well-written and contains a good introduction.
I've observed that too many users of jq aren't willing to take a few
minutes to understand how stream programming works. That investment
pays off in spades.
penguin_booze wrote 12 hours 44 min ago:
I'm a big fan of jq but won't credit its man page with much. There
were (ineffable) insights that I picked up through my own usage over
time, that I couldn't glean from reading the man page alone. In other
words, it's not doing its best to put the correct mental model out
for a newish user.
MrApathy wrote 17 hours 34 min ago:
Plugging a previous personal project for learning jq interactively:
URI [1]: https://jqjake.com/
wpm wrote 21 hours 30 min ago:
Also, LLMs are good at spitting out filters, but you can learn what
they do by going and then looking up what itâs doing in the docs.
They often apply things in far more interesting and complex ways than
the docs at jqlang.org do, which are often far too âfoo bar bazâ
tier to truly understand explain the power of things.
roxolotl wrote 23 hours 14 min ago:
I hate jq as much as the next guy but itâs ubiquitous and great for
this sort of thing. If you want a single path style query language
Iâd highly recommend JsonPath. Itâs so much nicer than jq for âI
need every studentâs gpaâ.
cryptonector wrote 23 hours 30 min ago:
You just have to wrap your mind around jq. It's a) functional, b) has
pervasive generators and backtracking. So when you write `.a[].b`,
which is a lot like `(.a | .[] | .b)` what you get is three generators
strung together in an `and_then` fashion: `.a`, then `.[]`, and then
`.b`. And here `.a` generates exactly one value, as does `.b`, but
`.[]` generates as many values as are in the value produced by `.a`.
And obviously `.b` won't run at all if `.a` has no values, and `.b`
will run for _each_ value of `.a[]`. Once you begin to see the
generators and the backtracking then everything begins to make sense.
movpasd wrote 10 hours 10 min ago:
I think this is a paradigm known as concatenative programming:
URI [1]: https://en.wikipedia.org/wiki/Concatenative_programming_lang...
ccvannorman wrote 23 hours 34 min ago:
I can't help myself and surely someone else has already done the same.
But the query
obj.friends.filter(x=>{ return x.city=='New York'})
.sort((a, b) => a.age - b.age)
.map(item => ({ name: item.name, age: item.age }));
does exactly the same without any plugin.
am I missing something?
joshribakoff wrote 23 hours 8 min ago:
The verbosity.
To your point abstractions often multiply and then hide the
complexity, and create a facade of simplicity.
ivanjermakov wrote 23 hours 57 min ago:
jq is amazing (not only for querying json), I recommend going though
the docs, it's fairly small.
I implemented one day of advent of code in jq to learn it:
URI [1]: https://github.com/ivanjermakov/adventofcode/blob/master/aoc20...
hk1337 wrote 1 day ago:
What do your users know? If theyâre quite familiar with SQL for
querying, I would look at duckdb.
hyperhello wrote 1 day ago:
.friends
| filter(.city == "New York")
| sort(.age)
| pick(.name, .age)
mapValues(mapKeys(substring(get(), 0, 10)))
This is all too cute. Why not just use JavaScript syntax? You can limit
it to the exact amount of functionality you want for whatever reason it
is you want to limit it.
mrtimo wrote 1 day ago:
DuckDB can read JSON - you can query JSON with normal SQL.[1]
I prefer to Malloy Data language for querying as it is 10x simpler than
SQL.[2] [1] - [1] [2] -
URI [1]: https://duckdb.org/docs/stable/data/json/overview
URI [2]: https://www.malloydata.dev/
zie wrote 22 hours 10 min ago:
So can postgres, I tend to just use PG, since I have instances
running basically everywhere, even locally, but duckdb works well
too.
peterohler wrote 1 day ago:
If you prefer JSONPath as a query language, oj from [1] provides that
functionality. It can also be installed with brew. (disclaimer, I'm the
author of OjG)
URI [1]: https://github.com/ohler55/ojg
tyre wrote 1 day ago:
JSONPath is also supported by Postgres!
Helpful when querying JSON API responses that are parsed and
persisted for normal, relational uses. Sometimes you want to query
data that you werenât initially parsing or that matches a fix to
reprocess.
Eric_WVGG wrote 1 day ago:
speaking of classic databases: can anyone explain to me, a dummy,
why any syntax like this or even GraphQL is preferable to "select
a.name, a.age from friends a where a.city = 'New York' order by
a.age asc"?
eknkc wrote 1 day ago:
Most alternatives being talked about are working on query strings (like
`$.phoneNumbers[:1].type`) which is fine but can not be easily modeled
/ modified by code.
Things like [1] works better if you wish to expose a rest api with a
defined query schema or something like that. Instead of accepting a
query `string`. This seems better as in you have a string format and a
concrete JSON format. Also APIs to convert between them.
Also, if you are building a filter interface, having a structured
representation helps:
URI [1]: https://jsonlogic.com/
URI [2]: https://react-querybuilder.js.org/demo?outputMode=export&expor...
throwaway091025 wrote 1 day ago:
JSON logic is nice, but for example, the Python bindings were last
updated 8 years ago
HatchedLake721 wrote 1 day ago:
[1] or
URI [1]: https://jsonpath.com/
URI [2]: https://jsonata.org/
wofo wrote 1 day ago:
Would you mind sharing a bit more? Have you used them? How did that
go?
gnarlouse wrote 1 day ago:
I use `jsonata` currently at work. I think it's excellent. There's
even a limited-functionality rustlib ( [1] ). What I particularly
like about `jsonata` is its support for variables, they're super
useful in a pinch when a pure expression becomes ugly or unwieldy
or redundant. It also lets you "bring your own functions", which
lets you do things like:
```
$sum($myArrayExtractor($.context))
```
where `$myArrayExtractor` is your custom code.
---
Re: "how did it go"
We had a situation where we needed to generate EDI from json
objects, which routinely required us to make small tweaks to data,
combine data, loop over data, etc. JSONata provided a backend
framework for data transformations that reduced the scope and
complexity of the project drastically.
I think JSONata is an excellent fit for situations where companies
need to do data transforms, for example when it's for the sake of
integrations from 3rd-party sources; all the data is there, it just
needs to be mapped. Instead of having potentially buggy code as
integration, you can have a pseudo-declarative jsonata spec that
describes the transform for each integration source, and then just
keep a single unified "JSONata runner" as the integration handler.
URI [1]: https://github.com/Stedi/jsonata-rs
montekristooGDB wrote 11 hours 25 min ago:
I confirm.
At first I was trying to write that "buggy" code, until I got
jsonata, and started working with the queries it supports.
It made my life a lot easier
mediaman wrote 1 day ago:
We've had a great experience with JSONata too.
It's nice because we can just put the JSONata expression into a
db field, and so you can have arbitrary data transforms for
different customers for different data structures coming or
going, and they can be set up just by editing the expression via
the site, without having to worry about sandboxing it (other than
resource exhaustion for recursive loops). It really sped up the
iteration process for configuring transforms.
pscanf wrote 1 day ago:
I have a similar use case in the app I'm working on. Initially I went
with JSONata, which worked, but resulted in queries that indeed felt
more like incantations and were difficult even for me to understand
(let alone my users).
I then switched to JavaScript / TypeScript, which I found much better
overall: it's understandable to basically every developer, and LLMs are
very good at it. So now in my app I have a button wherever a TypeScript
snippet is required that asks the LLM for its implementation, and even
"weak" models one-shot it correctly 99% of the times.
It's definitely more difficult to set up, though, as it requires a
sandbox where you can run the code without fears. In my app I use
QuickJS, which works very well for my use case, but might not be
performant enough in other contexts.
arccy wrote 1 day ago:
In the k8s world there's a random collection of json path, json query,
some random expression language.
Just use jq. None of the other ones are as flexible or widespread and
you just end up with frustrated users.
voidfunc wrote 1 day ago:
This. Jq is the defacto standard and anytime I come across something
else I am annoyed.
Which isn't to say jq is the best or even good but its battle-tested
and just about every conceivable query problem has been thrown at it
by now.
lenkite wrote 1 day ago:
There are a ridiculous number of JSON query/path languages. Wish all
the authors got together and harmonized on a standard.
cmckn wrote 1 day ago:
The AWS CLI supports JMESPath ( [1] ) for the `--query` flag. I don't
think I've run into anything else that uses it. Pretty similar to
JSONPath IIRC.
URI [1]: https://jmespath.org
gegtik wrote 1 day ago:
azure tools also support JMESPath
voidfunc wrote 1 day ago:
The standard is called jq, any new standard is just going to be a
committee circle jerk that doesn't move the ball forward in any
meaningful way.
lenkite wrote 1 day ago:
jq is good but its syntax is strangely unmemorizable. Have used it
for a decade and always need to look at the manual or at examples
to refresh my knowledge.
thayne wrote 1 day ago:
There is a standard in RFC 9535 (JSONPath)[1]. But as far as I can
tell, it isn't very widely used, and it has more limited
functionality than some of the alternatives.
[1]
URI [1]: https://datatracker.ietf.org/doc/html/rfc9535
phpnode wrote 23 hours 24 min ago:
the issue with JSONPath is that it took 17 years for it to become a
properly fleshed-out standard. The original idea came from a 2007
blog post [0], which was then extended and implemented subtly
differently dozens of times, with the result that almost every JSON
Path implementation out there is incompatible with the others.
[0]
URI [1]: https://goessner.net/articles/JsonPath/
lelandbatey wrote 1 day ago:
Don't forget the also standardized way of referring to a single
value in JSON, "JSON Pointer":
datatracker.ietf.org/doc/html/rfc6901
NewJazz wrote 1 day ago:
Postgresql supports jsonpath, right?
Groxx wrote 1 day ago:
SQLite might too, though I'm struggling to find anything explicit
about the syntax: [1] it might just be a very limited subset?
URI [1]: https://sqlite.org/json1.html#jptr
NewJazz wrote 22 hours 22 min ago:
Looks like "JSON Pointer":
datatracker.ietf.org/doc/html/rfc6901
thayne wrote 4 hours 6 min ago:
JSON pointer uses very different syntax. Sqlite looks like it
uses something that is very similar to, but not quite
compatible with, JSONPath.
miohtama wrote 1 day ago:
Xkcd.gif
nartho wrote 1 day ago:
Plus, I feel like most, if not all, higher level languages already
come with everything you need to do that easily. Well except for go
that requires you to create your own filter function.
linhns wrote 1 day ago:
Nice work with a jq-esque feel. Website is cut on mobile devices though
memelang wrote 1 day ago:
I've been working on an ultra-token-efficient LLM-friendly query
language.
URI [1]: https://memelang.net/09/
gnarlouse wrote 1 day ago:
Cool idea! Although without looking closer I can't tell if "meme" is
in reference to the technical or the colloquial meaning of meme.
Admittedly I don't know that much about LLM
optimization/configuration, so apologies if I'm asking dumb
questions. Isn't the value of needing to copy/paste that prompt in
front of your queries a huge bog on net token efficiency? Like
wouldn't you need to do some hundred/thousand query translations just
to break even? Maybe I don't understand what you've built.
Cool idea either way!
memelang wrote 23 hours 15 min ago:
Thank you. That script prompt is just for development and
exploration. A production model needs to be trained/fine-tuned on
Memelang first. We're working on this now. The math says we can
deliver a model 1/2 the size of an equivalent model for SQL.
npodbielski wrote 1 day ago:
Nice. I work on something similar but for .net.
gabrielsroka wrote 1 day ago:
They have an implementation for .net
URI [1]: https://jsonquerylang.org/implementations/#net
npodbielski wrote 9 hours 40 min ago:
Interesting. But looks like it require JSON object. My query
language works on top of Linq so it make it compatible with ORMs,
IEnumerable and IQueryable.
tcdent wrote 1 day ago:
Doesn't the command-line utility `jq` already define a protocol for
this? How do the syntaxes compare?
(LLMs are already very adept at using `jq` so I would think it was
preferable to be able to prompt a system that implements querying
inside of source code as "this command uses the same format as `jq`")
inlined wrote 23 hours 34 min ago:
Mongo also has a good query language and a mongo DB can be seen as an
array of documents
jonny_eh wrote 1 day ago:
For convenience:
URI [1]: https://en.wikipedia.org/wiki/Jq_(programming_language)
cryptonector wrote 23 hours 34 min ago:
Oh wow, it got undeleted. Some editor insisted on deleting it
because it was a "personal project" (Stephen Dolan's) even though
it has a huge user base. I guess now that it has a proper "org" in
GitHub it's different. What nonsense.
ancarda wrote 11 hours 21 min ago:
It's likely because there's a citation in a paper. That's
apparently the bar you need to reach to get Wikipedia to see
something as significant enough. I tried to get a draft article
about SourceHut ( [1] ) to be published after extensive
improvements and they refused because there weren't enough third
party links. This is despite the fact there's like a dozen pages
in Wikipedia about software that is hosted on SourceHut, so it
seems notable enough?
URI [1]: https://sourcehut.org/
rendall wrote 23 hours 24 min ago:
Wikipedia is such a disappointment
millerm wrote 3 hours 15 min ago:
How could you even type such a ridiculous statement?
lioeters wrote 2 hours 53 min ago:
Seriously, Wikipedia has been of immense value to society and
education.
Yes there are issues with ideologically motivated moderators,
poorly cited articles, etc. But even with its flaws, it's an
amazing resource provided to the public for free (as in
coffee and maybe as in speech also).
jonny_eh wrote 23 hours 26 min ago:
Maybe it helped that they called it a "programming language"? It
helps make it sound super serious.
gfody wrote 1 day ago:
not to be confused with jq for querying json?
jawns wrote 1 day ago:
I'd like to know how it compares to
URI [1]: https://jsonata.org
aeberhart wrote 7 hours 59 min ago:
We wrote an article on this: "JQ vs. JSONata: Language and Tooling
Compared".
URI [1]: https://dashjoin.medium.com/jq-vs-jsonata-language-and-tooli...
gnarlouse wrote 1 day ago:
JSONata looks to be more general purpose with its support for
variables/statements, and custom functions. I'd probably still stick
with JSONata
Alifatisk wrote 1 day ago:
Can't you just visit both pages, build an understanding and compare
them?
OrderlyTiamat wrote 1 day ago:
Maybe the author would be in a better place to do that, having the
expertise already. Also, as a user I'm quite happy with jq already,
so why expend the effort?
DIR <- back to front page