DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Cover image for Exact match search deeply nested objects - PostgreSQL
devlazar
devlazar

Posted on

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: {...}
}
Enter fullscreen mode Exit fullscreen mode

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"}]}';
Enter fullscreen mode Exit fullscreen mode

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!

Buy Me a Coffee at ko-fi.com

Have a nice time hacking! 😊

Top comments (0)

Welcome!

πŸ‘‹ Have You Posted on DEV Yet?

Head over to our Welcome Thread and tell us a bit about yourself!