DEV Community

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

Posted on

3 1

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

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

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

AWS GenAI LIVE!

GenAI LIVE! is a dynamic live-streamed show exploring how AWS and our partners are helping organizations unlock real value with generative AI.

Tune in to the full event

DEV is partnering to bring live events to the community. Join us or dismiss this billboard if you're not interested. ❀️