PSQL: Count number of wildcard values in JSONB array

Question

My table has a jsonb column that stores JSON arrays of strings in this format:

["ItemA", "ItemB", "ItemC"]

I'm trying to filter the rows based on the number of certain items in the array, using a wildcard for a part of the name of the item.

From what I have read here on SO, I could use the jsonb_to_recordset function and then just query the data normally, but I can't put the pieces together.

How do I use the jsonb_to_recordset to accomplish this? It's asking for a column definition list, but how do I specify one for just a string array?

My hypothetical (but of course not valid) query would look something like this:

SELECT * FROM mytable, jsonb_to_recordset(mytable.jsonbdata) AS text[] WHERE mytable.jsonbdata LIKE 'Item%'

EDIT: Maybe it could be done using something like this instead:

SELECT * FROM mytable WHERE jsonbdata ? 'Item%';

Answer 1

Use jsonb_array_elements():

select *
from 
    mytable t, 
    jsonb_array_elements_text(jsonbdata) arr(elem)
where elem like 'Item%';

          jsonbdata          | elem  
-----------------------------+-------
 ["ItemA", "ItemB", "ItemC"] | ItemA
 ["ItemA", "ItemB", "ItemC"] | ItemB
 ["ItemA", "ItemB", "ItemC"] | ItemC
(3 rows)

Probably you'll want to select only distinct table rows:

select distinct t.*
from 
    mytable t, 
    jsonb_array_elements_text(jsonbdata) arr(elem)
where elem like 'Item%';