
Postgres 9.3 feature highlight: JSON operators - stesch
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
======
jtchang
Damn that is how you write a git commit message.

~~~
masklinn
An other fine example — from the same history — was the commit fixing the
security issue 2 weeks ago:
[http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitd...](http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a6e0cd7b76c04acc8c8f868a3bcd0f9ff13e16c8)

This is an absolutely beautiful commit message

------
eknkc
So, can these be mixed with functional indexes to index on json fields?

~~~
hannibalhorn
Currently, it doesn't seem so - most of the functions have their volatility
declared as stable, not immutable, so you can't simply create an index on the
function without getting an erorr. I think long term the postgresql guys plan
on supporting special GiST indexes for JSON, but that didn't make it into 9.3.

Many of the presentations on using plv8 to access JSON in Postgres 9.2 (e.g.,
[https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-
fo...](https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-
fosdem-2013.pdf) ) show the use of functional indexes, but they create a plv8
function marked as immutable.

I imagine you _could_ create a wrapper function marked immutable that calls
the provided json accessor functions, but I'm not really clear on the
implications of doing so.

~~~
hannibalhorn
And it turns out that marking many of the JSON functions stable was an
oversight and was corrected in git a few days ago:
[http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...](http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d788121aba3425b5d52fe20cc9facb6aca1294ce)

So now it is indeed possible!

------
jared314
Now, if it only had EDN[0] support too.

[0] <https://github.com/edn-format/edn>

~~~
jacques_chester
Serious question: why?

~~~
jared314
In the general case, it has the low startup cost of a document db combined
with a clear path to scalability with a future relational model.

In the specific case, it removes the data transformation code required to
convert the clojure data structures I have to sql, json, or xml that
postgresql understands.

~~~
jacques_chester
So the same argument as (say) YAML, Lua tables or TNetStrings. Or, if you
include binary representations, stuff like Thrift, XDR and ASN.1.

You can embed a JVM in PostgreSQL and write the functions yourself (or use
PL/Scheme I suppose), but I would be basically amazed if anyone decides to do
it for you.

JSON gets the nod because it's understood by billions of systems. Other
formats are going to struggle.

~~~
jared314
Well, when you say it that way, I guess I must be wrong to want things that
simplify my life as a developer.

~~~
jacques_chester
It just seems like a particularly parochial wish, is all. And in life we
rarely get what we want because potential ends are limitless and means
limited.

~~~
jared314
It is a small wish, and you are trolling.

~~~
jacques_chester
It is not a small wish to ask them to take on supporting a serialisation
format. They will need to integrate it into the core code and it will need to
be supported indefinitely.

I asked because I wanted to know if there was a particular reason for your
wishes -- which is, as I said, quite parochial compared to the reach of JSON
-- over other potential priorities. If asking questions is "trolling", I'll be
over here with 4chan and Socrates.

------
NatW
You can find more detail on this here:
[http://www.postgresql.org/docs/devel/static/functions-
json.h...](http://www.postgresql.org/docs/devel/static/functions-json.html)

------
astro1138
It would be lovely if they could add support functions for:

RFC6901: JSON Pointer

RFC6902: JSON Patch

------
perlpimp
Are there some or other kinds of indexes on JSON data fields? The thing is I
would not need any kind of JSON manipulation unless it includes INDEX assisted
queries. Doing linear lookups is pretty stupid even if it includes fancy
accessors and validators for field formatting. I am far from being considered
expert on the topic .. but there is no mention of calculated indexes on JSON
formatted fields...

\-- are there indexes that assisted queries against JSON datastructures stored
in JSON type fields?

------
pointyhatuk
Ugh another thing I really don't want in the database. Seriously, stuff like
this will knacker your scalability over time.

I only say this because I've been there, with SQL Server's XML processing
stuff, then spent nearly 2 years getting rid of it.

~~~
pilif
Can you elaborate on the scaling issues? Being able to query into JSON fields
(including functional indexes) is a great helper for denormalization which is
very good for actually increasing scalability.

For me, the native JSON support is a very handy tool to have in the toolbelt
for parts of our application that have a very loose schema.

~~~
pointyhatuk
Well your database is a black box technically speaking. It's very hard to
scale it horizontally and it is very expensive to scale it vertically as time
goes on.

Logic suggests that you should keep as much processing functionality outside
something which can't be scaled cheaply or easily and push it to cheaper front
end servers.

On this basis, anything which implies more work than collecting and shifting
the data over the wire shouldn't really be in the database. Parsing /
processing JSON is one of those things that's going to eat CPU/memory.

Fundamentally there's nothing wrong with storing JSON inside the database and
processing it externally, but processing it inside the database is a big risk.

I've seen the same thing over the years with XML in the database and more
recently people adding CLR code to SQL Server stored procedures.

------
sergiotapia
For some reason this website wants me to run a Java applet. Nope. I'm going to
flag this submission and maybe a mod can shed some light into this.

~~~
stesch
I hope you flag everything with Flash, too.

~~~
dgesang
Better start flagging JavaScript as well.

~~~
stesch
Now we are talking!

