[question] denormalized data via jsonb

[toddself]

Is there any ability to use this with the jsonb column type and be able to filter based on data available within the object stored there?

We are making heavy use of partially normalized data (with joi as our validation schema for for the denormalized blobs) and it seems like it would be an interesting fit for this.

I’d be happy to investigate a PR if this is of interest or possibly in scope.

[chrisdickinson]

Yes! This should totally be possible & I'd totally love a PR if you have the time. Maybe something like:

Model.object.filter({
  'json_column > field1 > field2:gt': 3, // equiv to json_column #> {field1, field2}
  'json_column >> field1 >> field2:lt': 2 // equiv to json_column #>> {field1, field2}
})

// or, we could treat the json column as a psuedo-joinable table, which would make
// normalizing json columns in client code easier down the line?
Model.object.filter({
  'json_column.field1.field2:gt': 3, // equiv to json_column #> {field1, field2}
  'json_column.field1.field2:lt': 2 // equiv to json_column #>> {field1, field2}
})

[toddself]

sorry for the very long reply here -- i've been working through some ideas here but haven't had much time to commit any code around it.

my initial stabs at it were more on the second example you've got there, but i'm going to keep plugging away at this.

thanks for the tips!