Postgres jsonb column select from array where a string matches wildcards

Question

I'm looking to use postgres jsonb columns with arrays and being able to filter on them, so I have a schema with a column:

CREATE TABLE testtable
(
id  varchar(32) NOT NULL,   
refs    jsonb           NULL,
)

and the column contains data in the Json format:

{ "refs": ["one-1-0", "two-3-2", "two-3-4" ] }

I would like to be able to return all rows that that contain an array element that starts with (for example) "two-3-"

I have tried several things and can not get it to work as I'd like (the closest I have is to get the array part as text and search it as a string - but this is nasty)

I would also like to add a suitable index to this column to support this query.

Any suggestions would be briliant and greatfully received! Thanks

Possible duplicate of Postgresql search in array in json array — Barbaros Özhan
– Barbaros Özhan, Commented Sep 13, 2019 at 11:21

S-Man · Accepted Answer · 2019-09-13 11:17:44Z

5

step-by-step demo:db<>fiddle

SELECT DISTINCT                                   -- 3
    id, refs
FROM
    testtable,
    jsonb_array_elements_text(refs -> 'refs')     -- 1
WHERE
    value LIKE 'two_3%'                           -- 2

Expand the array into one row per array element
Filter all records by the array elements (value) using the LIKE comparator and the % wildcard
If there are several occurences then one record per occurence is being given back. So if you only want the original data set, you can remove the duplicates with DISTINCT

answered Sep 13, 2019 at 11:17

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rob Over a year ago

Thank you so much, I'd upvote this 100 times if possible! :)

Rob Over a year ago

Am I correct in thinking that the following index is the correct one to support this query: create index idx1 ON testtable((refs->'refs'))

S-Man Over a year ago

I believe you could try this at least.

Collectives™ on Stack Overflow

Postgres jsonb column select from array where a string matches wildcards

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related