0

I have the JSON column in my table which is having array's of dictionary. The array has standard format.

[{'path': 'chat/xyz.pdf', 'file_name': 'xyz.pdf'},
 {'path': 'chat/xyl.pdf', 'file_name': 'xyl.pdf'}]

The table name is chat and column name is attachments. I want to perform search on file names such that even if i type one letter is typed then that row should be retrieved. For example: if i search by string 'pd' then all values with file_name having 'pd' string should be retrieved.

Aaroosh Pandoh
  • 105
  • 11
  • Unrelated to your problem, but: Postgres 9.3 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. –  Mar 24 '20 at 06:04

2 Answers2

0

I tried this and it did work.

select distinct attachments from chat, jsonb_array_elements_text(attachments)
where value::json->>'file_name' like '%xyz%';

I took reference from documentation.

Aaroosh Pandoh
  • 105
  • 11
0

You can use an EXISTS condition, then you don't need the DISTINCT:

select c.*
from chat c
where exists (select *
              from jsonb_array_elements(c.attachments) as t(doc)
              where t.doc ->> 'file_name' like '%xyz%');