3 ms·

why not make the date values a real column? does it need to be inside the JSON?

• rotten 10y ago
  why not do both?
• darklajid 10y ago
  The use case I'm looking at is a legacy application that allows you to define various 'dynamic' fields. Think document, extracted data and you can expose fieldA, fieldB and fieldC, while another user wants D, E and F. Right now that application stores that in a rather messy way. Exposing this as a table field would mean that configuration changes (New field/a different field) causes a structural change (alter table). Or you have to go down the 'Date1, Date2, Date3, Number1, Number2' ... path. In short: I see no way to expose these pieces of information in fields without ugly hacks, hence my interest in HStore/JSON(B) - but datetime based filters would be quite nice to have..
  • s_kilk 10y ago
    Re datetime filters: If the datetimes are ISO formatted strings, would regex matching not work for basic filtering? where json_field.datetime similar to '2016-07-.*' Of course, it'll probably be slow-tastic, and less convenient than native datetime manipulation, but hey, maybe it's enough to get unblocked?
  • garyclarke27 10y ago
    You can use an expression index, which casts to timestamptz, would then be very fast to query and filter on such.