5 ms·

Postgres 9.3 feature highlight: JSON operators

86 points by stesch 14y ago

sergiotapia 14y ago
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 14y ago
  I hope you flag everything with Flash, too.
  - dgesang 14y ago
    Better start flagging JavaScript as well.
    - stesch 14y ago
      Now we are talking!
eknkc 14y ago
So, can these be mixed with functional indexes to index on json fields?
- saurik 14y ago
  Is there any reason to believe that you would not? I would be very surprised if there was any "pure" expression in PostgreSQL on which you couldn't have an index over comparison.
- hannibalhorn 14y ago
  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-fosdem-2013.pdf https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fo... ) 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 14y ago
    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;h=d788121aba3425b5d52fe20cc9facb6aca1294ce http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;... So now it is indeed possible!
jtchang 14y ago
Damn that is how you write a git commit message.
- masklinn 14y ago
  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=commitdiff;h=a6e0cd7b76c04acc8c8f868a3bcd0f9ff13e16c8 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitd... This is an absolutely beautiful commit message
- ajtaylor 14y ago
  I've been trying to do the same thing with my commit messages. It's just me for the moment, but knowing the why along with the what is a goldmine when you have to come back to some code 6 months later. Even better, vim has a git-commit mode to help remind me of the conventions.
pointyhatuk 14y ago
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 14y ago
  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 14y ago
    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.
- coldtea 14y ago
  Actually people USE JSON blob fields to increase scalability (as far as back as FriendFeed). Sure, doing this hampers normalization and single server speed, but if the queries are parallelizable with shards et al, what would hamper scalability?
- jacques_chester 14y ago
  Actually, these functions seem to support designs where the database is normalised, but front ends are emitting JSON.
jared314 14y ago
Now, if it only had EDN[0] support too. [0] https://github.com/edn-format/edn https://github.com/edn-format/edn
- jacques_chester 14y ago
  Serious question: why?
  - jared314 14y ago
    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 14y ago
      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 14y ago
        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 14y ago
        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.
astro1138 14y ago
It would be lovely if they could add support functions for: RFC6901: JSON Pointer RFC6902: JSON Patch
NatW 14y ago
You can find more detail on this here: http://www.postgresql.org/docs/devel/static/functions-json.html http://www.postgresql.org/docs/devel/static/functions-json.h...
perlpimp 14y ago
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?
HNLogInShit 14y ago
Why store JSON in a DB column?

86 points by stesch 14y ago

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 14y ago
I hope you flag everything with Flash, too.
- dgesang 14y ago
  Better start flagging JavaScript as well.
  - stesch 14y ago
    Now we are talking!

eknkc 14y ago

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

saurik 14y ago
Is there any reason to believe that you would not? I would be very surprised if there was any "pure" expression in PostgreSQL on which you couldn't have an index over comparison.
hannibalhorn 14y ago
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-fosdem-2013.pdf https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fo... ) 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 14y ago
  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;h=d788121aba3425b5d52fe20cc9facb6aca1294ce http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;... So now it is indeed possible!

jtchang 14y ago

Damn that is how you write a git commit message.

masklinn 14y ago
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=commitdiff;h=a6e0cd7b76c04acc8c8f868a3bcd0f9ff13e16c8 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitd... This is an absolutely beautiful commit message
ajtaylor 14y ago
I've been trying to do the same thing with my commit messages. It's just me for the moment, but knowing the why along with the what is a goldmine when you have to come back to some code 6 months later. Even better, vim has a git-commit mode to help remind me of the conventions.

pointyhatuk 14y ago

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 14y ago
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 14y ago
  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.
coldtea 14y ago
Actually people USE JSON blob fields to increase scalability (as far as back as FriendFeed). Sure, doing this hampers normalization and single server speed, but if the queries are parallelizable with shards et al, what would hamper scalability?
jacques_chester 14y ago
Actually, these functions seem to support designs where the database is normalised, but front ends are emitting JSON.

jared314 14y ago

Now, if it only had EDN[0] support too. [0] https://github.com/edn-format/edn https://github.com/edn-format/edn

jacques_chester 14y ago
Serious question: why?
- jared314 14y ago
  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 14y ago
    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 14y ago
      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 14y ago
        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.

astro1138 14y ago

It would be lovely if they could add support functions for: RFC6901: JSON Pointer RFC6902: JSON Patch

NatW 14y ago

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

perlpimp 14y ago

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?

HNLogInShit 14y ago

Why store JSON in a DB column?