DEV Community: Justin Chau

Working With JSON Objects Using SQL

Justin Chau — Tue, 07 Jun 2022 21:38:33 +0000

Recently, I have been teaching myself SQL to complete a project I have and one of the biggest problems I ran into was dealing with JSON objects in our companies data warehouse. I had no idea how to access the specific key pair values in the object, let alone access values within a JSON object wrapped in an array..

None of the StackOverflow threads or any online resource really helped or explained this concept in a way for me to understand. So after two-ish hours of playing around with my queries, I finally got it to work. In this small guide, hopefully I can explain this in a much more simple way for everyone to understand. Let's get to it!

The UNNEST Function

First it was essential for me to know about the UNNEST function and what it allowed me to do. Simply put, if there are values that you need and they are nested in an array, the UNNEST function allows us to pass in the array as an argument and lets us access these nested values. Let's see an example.

Say we have a column which is named values and has a value that looks like this:

['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']

If we wanted to get each individual value from this array, we would write a SQL query that looks like this:

SELECT *
FROM UNNEST(values) AS flattened_values;

This query would then give us a new column named flattened_values with each individual string in its own row like so:

Unnesting an Array of Objects

Easy enough right? Now that we understand that, lets move into unnesting an array of objects.

Lets use a similar example but this time, some of those strings are going to be in their own objects, wrapped in an array like so:

[{"name": "foo", "last_name": "bar"}]

In this case, we would need to make use of the UNNEST function as well as the JSON_EXTRACT_SCALAR function. Both of these functions working together help us reach into the array and extract a particular value from the object based on its key.

First we will need to use the UNNEST function to access the array and set that as a variable using the AS clause.

SELECT *
FROM UNNEST(names) AS flattened_names

Now that we have the name of the column that contains the array, which is set to flattened_names, we can select specific keys and grab their values using the JSON_EXTRACT_SCALAR function like so:

SELECT 
JSON_EXTRACT_SCALAR(flattened_names, '$.name') as first_name,
JSON_EXTRACT_SCALAR(flattened_names, '$.last_name') as last_name
FROM UNNEST(names) AS flattened_names

So what is exactly happening here? Well since we have the array set to the name of flattened_names we can access it through the JSON_EXTRACT_SCALAR function. This function extracts a scalar value which could be a string, number, or boolean. The first parameter we give the function is the array in which we want it to look at. In our case, we want it to look into the flattened_names array. The second parameter is going to be the name of the key whose value we want to extract. We prefix the key name with a "$.{key_name_here}". This effectively would be equivalent to accessing this key in an object with syntax like flattened_names.name.

Now that we've accessed the array, and extracted the individual values, running the query would then result in this:

Conclusion

So there you have it! My super simple run-down of something I learned in SQL and hopefully that helped some of you out there. If there is anything you wanna discuss on this, please post em down in the comments and if you liked this, make sure to give it a reaction ☺️

Hit me up on Twitter to stay up-to-date on when a new blog post comes out! Thanks for reading.

The Best Way To Learn on the Job

Justin Chau — Fri, 03 Jun 2022 23:54:57 +0000

I've been in the industry now for 2 years after getting my first job as a Developer Support Engineer at Plaid. I never knew what to expect but I knew that I still had a lot to learn. As a self-taught developer, there are naturally some gaps in my knowledge that I need to fill. The question I asked myself so much is "how in the hell am I going to learn all this?".

Being brand new to the role and the industry, I came in already overthinking every aspect of the job and feeling like there was this high expectation to deliver that I needed to hit. But I found out very early on what I had to do in order to fill in the gaps and bring as much impact as possible to the team!

And that was to:

Leverage Your Co-Workers!

There was an invaluable resource readily available to me that I completely overlooked.. And that was my co-workers. These are individuals that have been in the trenches way longer than I have! They have seen and done the things I dreamt of doing so I started to leverage them and became a sponge, soaking up as much knowledge as I could.

I asked them questions whenever I could. Being an introvert myself, it was hard at first to get out of my comfort zone and actually talk to people. Yes.. weird I know but I do suffer from social anxiety. Over the years, I have learned to somewhat overcome this. I'd love to tackle this in another blog post if people are interested!

Becoming a Sponge

Anytime I struggled on a certain bug/problem, I asked in related Slack channels so individuals from that respective team could give me an answer. If it still wasn't clear, I'd keep poking until I understood. Eventually I identified the people that explained certain concepts in a way I could understand and were willing to hop on calls to explain them further.

Those are the people I'd naturally gravitate towards and who I have learned from the most. Without them, it would've taken me even longer to learn something either from scratch or sifting through StackOverflow questions.

Conclusion

Moral of the story here is to use the resources that are right in front of you. It might seem intimidating at first (and trust me, I still get anxiety doing it now), but it will be the best thing you'll ever do.

In March of 2022, I started a new role as a Developer Advocate at Airbyte, making the transition from more of a Frontend focus to a Data Engineering focus. This in itself is one of the biggest challenges I am currently going through. But being surrounded by Data Engineers and Data Analysts, I know I will get through this 😉

I hope you enjoyed this blog post! Hit me up on Twitter if ya have any questions and to see when a new blog post comes out! See ya in the next one 🙏🏽

My MongoDB Atlas Hackathon Submission!

Justin Chau — Fri, 14 Jan 2022 03:44:42 +0000

Overview of My Submission

The application I built is a YouTube Video Planner which loosely simulates a Notion template I use to track what YouTube videos I plan on creating. I thought it would be a better idea to have a dedicated location where I can track these videos and what better way than to do it for this hackathon as my project!

As of right now, it is a very barebones application to where it essentially functions the same as a todo app but I do plan on working on this more. I built this with NextJS, Graphql, Apollo, Prisma and MongoDB as my stack of choice!

Submission Category:

Choose Your Own Adventure

Link to Code

Github repo

The One Thing You Should Prioritize As A Developer

Justin Chau — Mon, 26 Oct 2020 19:20:03 +0000

After talking with so many different developers and listening to their experiences, there is one thing that I learned to really focus on. It is something that we often overlook and never really pay attention to it until we reach the tipping point. This really needs to be at the forefront of the conversation when it comes to being a developer.

Thats Mental Health

Again, this is something we often overlook as devs. We can get carried away with "The Grind" or "The Hustle" when in reality, learning to code should be where we deploy patience and consistency over time. Not this idea of working/learning all day every day cause thats what we think we need to do to make it big as a developer. Completely not the case here.

Instead, we should take it one day at a time at a pace that is reasonable and maintainable. Learning how to control our pace will not only help us be more productive, but it will also keep us farther away from burnout. No one likes to burnout, especially when it comes to something we love to do! Which makes it even more important to stay at a steady pace.

Along with pacing, we need to take more time for our personal lives. That means taking breaks to go outside for a run, hangout with friends/family, read a book or anything that isn't related to your work! Having our minds wrapped around our work all the time is not only unhealthy, but counterintuitive. It is something I learned the hard way and don't want to see anyone else go through. I almost quit everything because of this very problem.

Conclusion

Short and sweet for my first blog post on a topic that is super important to me. Hope you got something out of that and if you want a more detailed explanation from me on this topic, check out my recent YouTube video that I made below! Let me know what you think in the comments!