4 ms·

We store settings based on a hierarchy in the database using JSONB. Basically: global -> country -> account -> user PostgreSQL doesn't support deep merging of

We store settings based on a hierarchy in the database using JSONB. Basically: global -> country -> account -> user PostgreSQL doesn't support deep merging of json docs, so I just wrote an aggregate function using plv8 to merge them. (the result from the query always results in ~10 records which is then grouped and merged so perf is always fast) https://gist.github.com/phillip-haydon/02e1cda346b4900a3e0090cefe85b176 https://gist.github.com/phillip-haydon/02e1cda346b4900a3e009... This doesn't include the javascript used for merging cos I just wanted to show the stub. This means we can just pass the args to postgresql and return the settings for any given user. We used to attempt to run 6 queries and merge in C# code when we did this in SQL Server.

williamstein 10y ago
I also had to write JSONB deep merging as part of this RethinkDB --> PostgreSQL rewrite. Here's the code I wrote https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf784ebd3e0cff2a7a695a73/src/smc-hub/postgres-base.coffee#L286 https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf78... That code constructs a single complicated query; I wonder whether I should write it as a stored function in the database instead. (I'm the author of the blog post.)
inopinatus 10y ago
I've deliberately avoided deep arrays for settings, so my jsonb settings merge is simply: SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb) FROM users JOIN companies ON users.company_id = companies.id LEFT JOIN groups ON users.group_id = groups.id WHERE users.id = ? Where I need a hierarchy for key names I'm just prefixing. I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.

williamstein 10y ago

I also had to write JSONB deep merging as part of this RethinkDB --> PostgreSQL rewrite. Here's the code I wrote https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf784ebd3e0cff2a7a695a73/src/smc-hub/postgres-base.coffee#L286 https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf78... That code constructs a single complicated query; I wonder whether I should write it as a stored function in the database instead. (I'm the author of the blog post.)

inopinatus 10y ago

I've deliberately avoided deep arrays for settings, so my jsonb settings merge is simply: SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb) FROM users JOIN companies ON users.company_id = companies.id LEFT JOIN groups ON users.group_id = groups.id WHERE users.id = ? Where I need a hierarchy for key names I'm just prefixing. I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.