
Postgres 9.2 highlight: JSON data type - craigkerstiens
http://michael.otacoo.com/postgresql-2/postgres-9-2-highlight-json-data-type/
======
dude_abides
FYI: Postgres also supports the hstore data type since 8.3. It is quite handy
if you want to store key-values in your table

<http://www.postgresql.org/docs/9.2/static/hstore.html>

~~~
pdog
Interesting. How does PostgreSQL with _hstore_ compare to a dedicated key-
value data store?

------
hiroprot
I think this will be really cool when we can index and query on parts of the
JSON structure, similar to hstore.

~~~
fdr
plv8.

    
    
        https://code.google.com/p/plv8js/
    

Besides the obvious (such as projecting a sub-datum from a json datum), you
can also do data transformations (whatever javascript can give you) -- as a
contrived example, the sine of a number or the sum of two numbers -- and, you
can try projecting arrays or hstores from a json and then using GIN/GiST to do
a more expensive but more powerful index over them.

A quick and dirty example from some time ago:
<https://gist.github.com/1152997>

Some more exotic built-in indexing will probably show up in the coming years,
but a lot of things can be accomplished relatively soon with this extension.

~~~
ibotty
that's right. plv8 works. but it certainly does not feel like using a postgres
index but a map-without reduce-framework. so i hope that built-in indexing
will come sooner than later... :D

------
saym
At first, I thought this wasn't a big deal because JSON really is just text.

Then I realized that this will help in validation. Go Postgres!

~~~
nnq
...maybe my perspective is different, but WHY would you want to do validation
of a "complex" field at the db level? (I mean, for a web app you end up with 3
(!!) layers of validation now: 1. client (javascript), 2. app (...), 3. db
(javascript? PL/something?) and, as an added "bonus", you loose the ability to
migrate to a different db

...all these db features seem like maintainance nightmare waiting to happen.
...why don't people keep the rdbms "slim" and mean and write the logic in your
app code, where it belongs? (you don't even have version control for the code
in stored db procedures and things like that!!)

~~~
ocharles
> WHY would you want to do validation of a "complex" field at the db level?

Anything I build on top of this database now benefits from all of my
consistency and integrity checks. While it won't prevent me from trying to put
bad data in, it will prevent me from actually doing the storage (instead, it
will remind me that I'm a terrible human and I forgot to validate certain
parts of data).

> I mean, for a web app you end up with 3 (!!) layers of validation now: 1.
> client (javascript), 2. app (...), 3. db (javascript? PL/something?)

This is a legitimate concern, but not all applications need all these checks.
Hopefully you've abstracted things apart enough so that you can build your
application reusing logic where possible.

> as an added "bonus", you loose the ability to migrate to a different db

I hear this one a lot, and it's so misguided imo. Different database systems
are implemented completely differently. If we strictly adhered to relational
algebra and the relational model then we might be in a position where you
could switch out databases and have the same performance. However, what we
have no doesn't work like that. Even if you strictly use standard SQL, I can
almost guarantee that for large databases you'll see difference performance
characteristics over the different database implementations.

> why don't people keep the rdbms "slim" and mean and write the logic in your
> app code, where it belongs?

That _isnt_ where it belongs at all. Your app should interact with the
database, but the database should have the final say in guaranteeing
consistency. This provides an excellent foundation that allow you to now layer
future projects on top of, without being tied to a specific application
framework/library/programming language. Remember, we already have the
impedance mismatch so whenever you map from the database to your application,
you make a decision about how that mapping is done. So even if you offload all
the database stuff to your 'app', you limit yourself to what you can truly do
with the database.

Seriously, let the database do its job and be a database.

> you don't even have version control for the code in stored db procedures and
> things like that!!

This is nonsense. <http://pyrseas.projects.postgresql.org/>
<https://github.com/theory/sqitch> <https://github.com/depesz/Versioning>. Do
your research.

------
liotier
Why would anyone store JSON data in a relational database, thus losing the
opportunity to join with the objects kept as JSON data ? I guess that I like
normalized data too much and that some people have performance constraints,
but still - a denormalized tables solves that problem and keeps output
formatting sanely out of the data model.

~~~
meaty
Well technically you don't join (document store approach) or you keep your IDs
and FK's as surrogates outside the JSON blob and use them as indexes only
(hyprid mongo-ish store).

What it does give you is a JSON object store with somewhat better stability
and _real_ transactional guarantees compared to say CouchDB or MongoDB.

However, the issue I have with this approach is more that there is some
opportunity to add more processing into the database black box, which is
expensive to scale up. I'd rather it wasn't type aware of JSON (i.e. it was
just a varchar(max) column) and all that processing sat in the application
itself which is cheap to scale out.

~~~
klaruz
CouchDB gives you /real/ transactions, they just don't span multiple
documents.

~~~
meaty
Well that's not real transactions - that's ACID compliance...

------
nnq
...is there any point in making a db so "fat" feature wise? ...isn't a text
field where I can serialize anyth anyway I like (with the related logic in my
app of course) enough for 99% and things like this a feature that only
benefits 1%?

~~~
ocharles
Your database should be the central storage of this data and it should do it's
hardest to guarantee that everything is consistent. Consistency means
referential integrity over foreign keys, but it also means that values of
attributes are well within their specified domain. And "D]7843bn;hdbnb" is not
valid JSON, so the database should reject that.

If your database is already doing these checks, then it can simplify anything
that builds on them, because you already have huge amounts of guarantees.

Other guarantees you would add are unique constraints, exclusion constraints,
check constraints, and so on.

So it might only benefit 1%, but what's the problem with that? If you want to
stuff JSON into a text field, no one is stopping you. Of course, now you'd
have much less reason to do so, and would be loading a gun getting ready to
shoot yourself in the foot...

~~~
nnq
...instead of "would be loading a gun getting ready to shoot yourself in the
foot" I'd see it as building an app that could easily be maintained by a/more
programmer(s) with no advanced DBA knowledge (less people with less expertise
needed, and have the really smart know-it-alls work on more interesting things
- and yes, I am thinking this from a MANAGER'S perspective, guilty on that ;)
)

