Comment by b0ti | Hacker News Reader

Comment by b0ti | Hacker News Reader

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.

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"}

VS

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".

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.

Reply on news.ycombinator.com