3 ms·

My biggest gripe with joining and aggregating to JSON is JSON itself. The project I'm working on is dealing with timestamp ranges which of course can't be seria

My biggest gripe with joining and aggregating to JSON is JSON itself. The project I'm working on is dealing with timestamp ranges which of course can't be serialized to JSON. Although I'm not working with SQLAlchemy (or Python) I presume it has the power to serialize a TSRANGE into a native Python range (if it has that) as well. I'm curious if anyone has ever found some way around that, I feel like it isn't possible UNLESS there's some data type than JSON available that I could aggregate with.

tqkxzugoaupvwqr 7y ago
I think it is a good thing JSON is limited in what types it supports. Otherwise it would contain so much type bloat while at the same time still annoying someone somewhere because his type is not supported by JSON. If you are able to pull your data from the database and process it in a programming language of your choice, you can write a custom serializer that takes a tsrange and spits out some JSON, e.g. a JSON array with two items [start, end], which you then can pass on to a client that understands your JSON format.
- mullsork 7y ago
  > I think it is a good thing JSON is limited in what types it supports. Otherwise it would contain so much type bloat while at the same time still annoying someone somewhere because his type is not supported by JSON. I wish it had a few more capabilities, but since JS doesn't then JSON won't. > If you are able to pull your data from the database and process it in a programming language of your choice, you can write a custom serializer that takes a tsrange and spits out some JSON, e.g. a JSON array with two items [start, end], which you then can pass on to a client that understands your JSON format. I can definitely do that. My case though is aggregating and serializing into JSON in postgres and loosing type information due already on the query level.
dragonwriter 7y ago
> The project I'm working on is dealing with timestamp ranges which of course can't be serialized to JSON. JSON is a low-level serialization format, on which you often need to build application-specific high-level formats. You can serialize a tsrange into JSON a number of ways, but your deserializer will.need to be aware of the higher-level format you create by your decisions on how to serialize unsupported types as composites of supported types.
- mullsork 7y ago
  Very fair to say that this logic ought to exist on the application level, I suppose.
munk-a 7y ago
I think this isn't really a failing as there will be custom data types as well, in our system we make use of interval ranges for describing age limitations tied to products we're working with (like [3 months,15 years]) this is already into the realm of non-standard postgres typing and asking JSON to support it would be silly - as other comments have mentioned this is really where you should be defining your own rules.

tqkxzugoaupvwqr 7y ago

I think it is a good thing JSON is limited in what types it supports. Otherwise it would contain so much type bloat while at the same time still annoying someone somewhere because his type is not supported by JSON. If you are able to pull your data from the database and process it in a programming language of your choice, you can write a custom serializer that takes a tsrange and spits out some JSON, e.g. a JSON array with two items [start, end], which you then can pass on to a client that understands your JSON format.

mullsork 7y ago
> I think it is a good thing JSON is limited in what types it supports. Otherwise it would contain so much type bloat while at the same time still annoying someone somewhere because his type is not supported by JSON. I wish it had a few more capabilities, but since JS doesn't then JSON won't. > If you are able to pull your data from the database and process it in a programming language of your choice, you can write a custom serializer that takes a tsrange and spits out some JSON, e.g. a JSON array with two items [start, end], which you then can pass on to a client that understands your JSON format. I can definitely do that. My case though is aggregating and serializing into JSON in postgres and loosing type information due already on the query level.

dragonwriter 7y ago

> The project I'm working on is dealing with timestamp ranges which of course can't be serialized to JSON. JSON is a low-level serialization format, on which you often need to build application-specific high-level formats. You can serialize a tsrange into JSON a number of ways, but your deserializer will.need to be aware of the higher-level format you create by your decisions on how to serialize unsupported types as composites of supported types.

mullsork 7y ago
Very fair to say that this logic ought to exist on the application level, I suppose.

munk-a 7y ago

I think this isn't really a failing as there will be custom data types as well, in our system we make use of interval ranges for describing age limitations tied to products we're working with (like [3 months,15 years]) this is already into the realm of non-standard postgres typing and asking JSON to support it would be silly - as other comments have mentioned this is really where you should be defining your own rules.