Re:RethinkDB versus PostgreSQL: my personal experience
by inopinatus on Friday February 10, 2017 @03:39AM
(#13614066)
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 = ?
I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.
Re:RethinkDB versus PostgreSQL: my personal experience
by williamstein on Friday February 10, 2017 @03:04AM
(#13613932)
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/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.)
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/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.