

JSON in Postgres and Node - JohnDotAwesome
http://j0.hn/24

======
andrewguenther
Actually, Postgres does have a JSON datatype. The only extra thing it does is
ensure the data is valid JSON, but it is still technically incorrect to say
that there is no JSON type.

[http://www.postgresql.org/docs/devel/static/datatype-
json.ht...](http://www.postgresql.org/docs/devel/static/datatype-json.html)

~~~
jvm
> The only extra thing it does is ensure the data is valid JSON,

Even that's actually not quite fair, it has special syntax for queries within
the JSON (->> etc), and a bunch of functions:
[http://www.postgresql.org/docs/devel/static/functions-
json.h...](http://www.postgresql.org/docs/devel/static/functions-json.html)

~~~
andrewguenther
These functions can be run on datatypes other than JSON, but yes, you are
correct.

------
hbbio
In related news (here: <https://news.ycombinator.com/item?id=5589593> ), the
Opa framework which is based on Node.js just released support for both MongoDB
and Postgres from the same application source.

------
audreyt
The plv8x project ( <https://github.com/clkao/plv8x> ) adds convenient JSON
and JS/LiveScript selector operators: [https://speakerdeck.com/audreyt/pgrest-
node-dot-js-in-the-da...](https://speakerdeck.com/audreyt/pgrest-node-dot-js-
in-the-database) covers the basic usage and the motivation, as well as the
ongoing PgREST work ( <https://github.com/clkao/pgrest> ) that builds a
MongoLab REST API server that resides within the database, so they can be
queried over a pg connection as well as through HTTP.

------
film42
Wouldn't it be a lot nicer to just use a schema and serializer? I know that's
not really the point of this article, but if you're trying to get a SQL db to
operate like a NoSQl store, it feels like a lot of hand-waving.

~~~
JohnDotAwesome
It really depends on the data structure. If you've got something that can have
an unknown amount of variables in it and you're already serving up JSON, then
I think using JSON is the way to go.

Another use-case: We've got hundreds of clients that send health statuses for
a ton of different metrics every 10 minutes. Stuff like Wifi strength,
exceptions caught/uncaught, various errors and crash reports, blah blah.
Anyway, we need a flexible store for all of this stuff because we're always
adding more metrics. Whatever the clients send as their request body gets
added as a JSON object.

We also want to dynamically display all of these metrics. We can literally
grab the data as JSON and make the keys table column headers in an html view.
Adding new metrics can automatically be reflected in both the database and in
our html views. We can query against new fields without changing schemas or
business logic.

~~~
film42
If it were up to me, I would create a schema for each metric and then if you
need all on one page, create some nice /client/client_id/metrics/all route.

Although if you're dealing with metrics, you probably want to have a good
defined model of your data (schema), because statisticians want to know what
they have to work with, and saying, "bunch 'o JSON" is not acceptable. Plus
there's things like null values to deal with. Yuk.

For my startup, we find the common denominator of social media data. For
example, a tweet is very unique so it's itself. But then there's more generic
types like photos. So then we look at photo services like flickr and
instagram. We then evaluate the data available and look for common
denominators again to hone in on as much similar data as possible. The result
is a model of photo data from a wide range of sources, but are now unified. We
could have just used instagram json and flickr json and tossed in the db, but
because we defined a schema/ models to get data from, we can sleep soundly
knowing the data we send to the frontend is fast and always valid.

~~~
tracker1
Until you have 20-30 joins for a single query, and it takes _forever_ to
return simple results against what should be a really simple query/search.

~~~
film42
This is very true, however it's a problem with your schema/ db types design/
caching. My main argument is using a random data blob is suicidal.

------
semihandy
Why would you want this over a NoSQL store? How does Postgres even index an
hstore?

~~~
scubaguy
Personally, I'd like the ability to make a transactional update to records in
different tables.

~~~
JohnDotAwesome
THIS. Exactly why Mongo fails (though I love mongo). No freakin' transactions!

