0

Please, see the table below:

| text_id | text_title     | topics_weights            |
| ------- | ---------------| --------------------------|
| 112233  | Greetings      | {"(123,0.9)", "(456,0.8)"}|
| 112234  | Congratulations| {"(789,0.8)", "(101,0.5)"}|
| 112235  | Salutations    | {"(123,0.8)", "(102,0.8)"}|

My question is how do I get all the rows from this table if I want to get all the text_ids that contain 123 in the topics_weights column? The data type in the topics_weights column looks complex, and I am not sure how to create the condition.

I will appreciate your help! Thank you!

Maria Makarova
  • 9
  • 3
  • This is unclear. Please write a better description and give sample data and table definitions to illustrate what you want. – Laurenz Albe Feb 16 '22 at 11:50
  • 1
    What data type exactly is that column? Please **[edit]** your question and add the `create table` statement ([Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557)) –  Feb 16 '22 at 11:50
  • Thank you for your comments! I have tried to clarify the description. – Maria Makarova Feb 16 '22 at 13:33
  • looks like topics_weights might be an array: if it is: https://stackoverflow.com/questions/7925050/is-there-a-multivalued-field-type-available-in-postgresql will guide you to https://www.postgresql.org/docs/current/arrays.html#ARRAYS-SEARCHING which shows `SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);` which you can then do `Select * FROM yourTable WHERE 123 = ANY (topics_Weights)` but lots of assumptions being made – xQbert Feb 16 '22 at 13:39

1 Answers1

0

You can use substr and position to get those particular values

select * from test where substr(topics_weights ,position('{"(' in topics_weights)+3,position(',' in topics_weights)-(position('{"(' in topics_weights)+3)) ='123';

dbfiddle: https://www.db-fiddle.com/f/ozt4mkok2jRs6nfownFiQd/0

tnavidi
  • 1,043
  • 6
  • 12