Unstructured Datatypes in Postgres – Hstore vs. JSON vs. JSONB

Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ... by b0ti on Thursday July 21, 2016 @07:43AM (#12136172)

Having to work with unstructured datatypes in Postgres and other RDBMS products was quite hard a few years back, now with hstore and JSONB in Postgres this has gotten much better but it still feels a bit unnatural. When we started the Raijin database project (http://raijindb.com) one of the goals was to make it possible to treat these extra attributes (that you would shove into JSONB with postgres) as normal "columns" so you can do this:

   CREATE TABLE tbl(eventtime datetime);
   INSERT INTO tbl {"eventtime":"2016-07-20 10:37:12","foo":"bar","whatever":"xx"};
   INSERT INTO tbl {"eventtime":"2016-07-20 10:38:22","foo":"bar2","intfield":42};
   SELECT foo, intfield FROM tbl;
   --
   {"foo":"bar","intfield":null}
   {"foo":"bar2","intfield":42}

It won't complain about the missing 'columns'.

Anyway, glad to see that Postgres is steadily making progress in this area.

[ Parent | on HN ]

Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ... by happyslobro on Thursday July 21, 2016 @08:13AM (#12136280)

Why does PG create fields with null values? Intfield was undefined when it was inserted for the `"foo": "bar"` row. Does this mean that if you insert 10K JSON rows, and each one has a unique field / key, then a SELECT * will return a 10K rows * 10K fields, most of which are null?

Having the DB automatically insert null fields also makes it harder to use Javascript's `Object.assign` to populate undefined fields with default values:

Object.assign({a: "default-a", b: "default-b"}, {a: "db-a"})

{a: "db-a", b: "default-b"}

Object.assign({a: "default-a", b: "default-b"}, {a: "db-a", b: null})

{a: "db-a", b: null}

null and undefined are two different concepts in JS. It is like the difference between "there is nothing here" and "I don't know if there is anything here".

[ Parent | on HN ]

Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ... by b0ti on Thursday July 21, 2016 @10:00AM (#12136844)

Note that this is not PG, Raijin is a new implementation and is not built on Postgres. Otherwise the question is valid. Raijin does not store NULL values though. In the above example a specific field (column) was selected and that's why it appears as null. Perhaps the output would be more appropriate this way then (which is a modification for us to consider):

  {"foo":"bar"}
  {"foo":"bar2","intfield":42}

Note that when you select the full record with the star it does not return null values:

  select * from tbl;
  --
  {"_id":1,"eventtime":"2016-07-20 10:37:12","foo":"bar","whatever":"xx"}
  {"_id":2,"eventtime":"2016-07-20 10:38:22","foo":"bar2","intfield":42}

The undef/exists thing can get a bit confusing when you are mapping this into SQL.

[ Parent | on HN ]