3 ms·
I have some tree data represented in jsonb fields -- is it possible to build a materialized view on top of recursive traversal of a jsonb data structure -- to e
by breatheoften 6y ago
I have some tree data represented in jsonb fields -- is it possible to build a materialized view on top of recursive traversal of a jsonb data structure -- to extract a view like this ...?
owner_id, element_path, element_id
where element_path would be something like an LTree -- to allow easily querying over the set of traversals defined in the jsonb?
Is it a reasonable way to represent the tree in convenient way for queryomg purposes while also allowing data updates to stay simple?
- golergka 6y agoWith ->> jsonb operator, writing a recursive query on such table to do a walk-through should be pretty straightforward, I think.
- barrkel 6y agoI did approximately that, but with actual columns, not jsonb, and with a normal view, not a materialized view. Performance sucked on the view because of all the lookups. So you could create the materialized view which builds the path, and then use an index on the path for subtree selection. However you might find refreshing the materialized view slower than you'd like. The only real answer here is to build it and load it out with a representative sample of data, or a set scaled down for a smaller test machine, and finding out what the latency and throughput are like for your case. Find out what the ground truth is for you, today, on today's hardware. My tests were from 4+ years ago.