The JavaScript/JSON PostgreSQL masterclass!

[forbesmyester]

Using and abusing PostgreSQL to write less code

Hi thinking about doing a talk about using PostgreSQL's JSON features but combining obvious things like WHERE, less obvious things like GROUP, WINDOW, WITH and subqueries and perhaps going as far as developing your own AGGREGATE functions.

I think, it is quite possible to swap vast swathes of code for advanced, but not unreadable SQL once you know where to look and what not to do, I would like to explore this idea.

[jkbits1]

@forbesmyester thanks for the proposal, your talks at LNUG over the years have always been great 👍 😄

I regularly deal with JSON stored in SQL dbs and heard recently that Postgres has some good features. It would be great to learn more about querying against the JSON.

I look forward to hearing this talk @lnug/contributors @lnug/organisers - any thoughts and feedback for Matt?

[forbesmyester]

Thanks @jkbits1 :-)

I will cover the basic json_b stuff but am more thinking about moving logic into queries...

For example we'd never use a Array.filter() in place of an SQL WHERE, but often we'll do an SQL query to get a list of ID's then do another SQL query with those ID's, why not use a WITH? Similarly we might well do grouping etc in code when we need more than SUM, MAX and AVG but we could use GROUP with a WINDOW functions to perform the same thing.

It seem to me there's a question about where we should be putting our complexity. This definitely has it's own pro's and con's, but I think, most people haven't even realised the question even exists and will be as surprised as I was at what is possible.

The super rough plan is to show a range of table/schema layout and a desired result, look at the JS for delivering it and then afterwards look at / explain the SQL for doing the same, perhaps thinking about the performance / scale-ability of going one way or the other.

I'll probably write a blog post, perhaps as a form of cheat sheet to support it.

[orliesaurus]

@forbesmyester - How do you feel about August 22nd?

[forbesmyester]

@orliesaurus I feel that is a bit tight for me right now, I have done no prep other than learning this stuff and having an idea it might make an interesting talk... Does it particularly fit in with other talks you have scheduled for that day? If so I probably can make the extra push but I would prefer a later date.

[orliesaurus]

@forbesmyester Okay - I was just asking because we might have another interesting talk coming up for August that works nicely in the theme of infra/db stuff - but I understand if it's tight for you no worries we can do Sept or November looking at the schedule - or maybe I will try to see if I can move things around :) You let me know!

[admataz]

Hi @forbesmyester - how are you for November 28th?

[forbesmyester]

I think that should be very achievable.

Thanks

[admataz]

You're on for November! will remind you closer to the time.... just one thing since you last spoke - we're asking speakers to confirm:

• [x] Talk will be ~20 minutes
• [x] You agree to our Code of Conduct

[forbesmyester]

Guys, I'm really sorry, I've realised that my employer has scheduled their Christmas party for the same day around a month ago and as it's only in my work calendar it's only now that I find they conflict.

I'm not going to be able to skip my work Christmas party so I'm going to have to drop out of this date and ask for one at another time :-(

Sorry to mess you around.

[admataz]

Hey @forbesmyester - thanks for letting us know in time! Let's arrange something for early 2019...

[admataz]

Hi @forbesmyester - let me know if you want to revive this idea for a talk... Thanks!

[forbesmyester]

Hi @admataz. I will, and I will do. I'm gonna finish my series of blog posts on this and then let you know.

Thanks

[admataz]

hey @forbesmyester - hope all is well with you? I'm doing some spring cleaning - is this talk still a potential thing? I'm closing for now - but feel free to reopen.

See you soon!