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

Cover image for how to search jsonb column in Postgresql ( not nested column )
Jackson Kasi
Jackson Kasi

Posted on

how to search jsonb column in Postgresql ( not nested column )

my schema :

create table blog (
  id  SERIAL PRIMARY KEY,
  title varchar,
  tags jsonb
);
Enter fullscreen mode Exit fullscreen mode

my db:
enter image description here

my query:

const tags = "horr";
const query = `SELECT * FROM blog where tags like '%${tags}%'`;
Enter fullscreen mode Exit fullscreen mode

what I am want:

now I am want to get only "tags" column include "horror". but I wanna use just "horr"

I know this query is not work. but wanna do like this bellow

"rows": [
    {
      "id": 1,
      "title": "horror stroy",
      "tags": [
        "sad",
        "horror"
      ]
    }
  ]
Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
jacksonkasi profile image
Jackson Kasi

can you please help @aarone4 @joelbonetr

Collapse
 
joelbonetr profile image
JoelBonetR • Edited on

The issue here is that you're trying to register an ΒΏArray? instead creating a relationship between blogs and tags.
Blog is an entity and tags is a different entity, and the relationship is N to N (N blogs can relate to N tags and each tag can be used in N posts).

Thus you need preferably an intermediate table to break the N-N relationship into two N-1, like this:

blog 1<->N blog_tags N<->1 tags

Please refer to the database normalization doc I added in the other post. You need to learn the basics first, we can't "work for you" solving your design flaws, you already have the specific learning material you need to begin your learning path on the topic, now it's your responsibility to put time and effort on it.

Thank you

Stop sifting through your feed.

Find the content you want to see.

Change your feed algorithm by adjusting your experience level and give weights to the tags you follow.