Exact match search deeply nested objects - PostgreSQL

Table Of Contents
* 🚀INTRO
* 🎯GOAL
* 🏁SOLUTION
* 🙏THANK YOU

🚀 Intro

Hi all! In this post I'll try to showcase the problem I faced recently. It is regarding the PostgreSQL database and how to query for an exact text match in the deeply nested objects and array of objects.

🎯 Goal

Let's consider that you are making a platform that allows people to register, create profile, add skills they poses and land a job offer. Let's assume that we are currently interested in creating a search engine that will search only for the exact match of the skill (e.g. "cooking") and It should return only the data that matched the search term.

Also, let's assume that we are storing the table that has a column called "sections" and it is of "jsonb" type. That object contains objects for "skills" (which contains an array of objects called "items"), "languages", "hobbies" etc.

Read more about JSON column in PostgreSQL docs

JSON object example

sections: {
   skills: {
      items: [{
         name: 'cooking'
      }]
   },
   languages: {...}
   hobbies: {...}
}

So, our current goal is to get the search term the "employer" user entered - in this case 'cooking' and find all the user profiles that match this term exactly.

🏁 Solution

We can achieve this in the following manner:

SELECT *
FROM user_profile up
WHERE up.sections->'skills' @> '{"items":[{ "name": "cooking"}]}';

This will return all the user profiles from the user_profile table, that matched the skill search term exactly.

🙏 THANK YOU FOR READING!

Please leave a comment, tell me about you, about your work, comment your thoughts, connect with me!

☕ SUPPORT ME AND KEEP ME FOCUSED!

Have a nice time hacking! 😊