Search code examples
postgresqlpostgresql-9.3json-query

Like operation on Json object in postgresql

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.

Solution

  • 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.