Question 89165 in PostgreSQL Question posted in
The official documentation can be found here.

How to get all records having a passed string in text column – Postgresql

What is the SQL query directly for this given use case:

I want to get all records in the table that in their fields column which is of type text has pageTitle containing a specific string

Table structure:enter image description here

This query that I created SELECT * FROM page_contents WHERE fields ILIKE '%"pageTitle":"%keyword%",%'; won’t do the job since it will search in the whole text after it encounters "pageTitle":" instead of stopping just after first occurance of ," if not found with pageTitle key. It will also return records that have keyword present anywhere after pageTitle for eg. for field like '"trackCategory":"LP DE BF 01","pageTitle":"some page title","metaDescription":"some meta description",' and consider keyword is meta. Then it will return this record with the field although we want to get records which have "pageTitle":"%meta%"," for eg. for field like '"trackCategory":"LP DE BF 01","pageTitle":"some meta title","metaDescription":"some description",'

Can it be achieved using direct SQL query with regex maybe or my only hope is to get all records and filter using javascript or my desired programming language?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Got the answer not exactly correct but works almost in every case:

    SELECT * FROM page_contents WHERE fields ~* '"pageTitle":"[^"]*keyword';

    Above query will only fail in case title has "(quote) present in it before our keyword


  2. 0

    Use regexp_like as follows – see regex101.com

    SELECT * FROM page_contents WHERE regex_like(fields, '/pageTitle":"some page title"/')

    Not sure if the / are necessary.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search