thurmc thurmc - 1 year ago 91
SQL Question

Querying using like on JSONB field

I have a field in a PostgreSQL database with a JSONB type in the format of

and I am trying to implement a search that will provide results for a predictive dropdown (i.e. if a user types "t" and the column above exists both tags are returned.

Any suggestions on how to do this?

I tried the query below but it is not working:

SELECT table.tags::JSONB from table where table.tags::TEXT like 't%';

Answer Source

One way you can do that is using jsonb_array_elements_text() function (

Example test:

FROM    jsonb_array_elements_text($$["tag1","tag2","xtag1","ytag1"]$$::jsonb)
WHERE   value LIKE 't%';
(2 rows)

Since jsonb_array_elements_text() creates set of records and in your case there is no other condition than LIKE then using LATERAL ( should help you out like this:

SELECT  T.tags
FROM    table T,
LATERAL jsonb_array_elements_text(T.tags) A
WHERE   A.value LIKE 't%';