thurmc thurmc - 3 months ago 8
SQL Question

Querying using like on JSONB field

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

["tag1","tag2"]
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

One way you can do that is using jsonb_array_elements_text() function (https://www.postgresql.org/docs/current/static/functions-json.html)

Example test:

SELECT  *
FROM    jsonb_array_elements_text($$["tag1","tag2","xtag1","ytag1"]$$::jsonb)
WHERE   value LIKE 't%';
 value
-------
 tag1
 tag2
(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 (https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-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%';
Comments