postgresql在jsonb格式下，如何使用regular express找尋陣列內容

今天有空終於解決了一個困擾我很久的問題，就是postgresql在jsonb格式下，不能直接使用regular express找尋陣列內容。

http://stackoverflow.com/questions/38204467/selecting-for-a-jsonb-array-contains-regex-match

跟上面網頁提到的情況一樣，有一條「{"single":"someText", "many":["text1", text2"]}」的jsonb資料，single是字串，可以用regular跑，如下

WHERE JsonBColumn ->> 'single' ~ '^some.*'

但many是陣列，預設只能用完全相同的方式尋找，例如

WHERE JsonBColumn -> 'many' ? 'text2'

想要把陣列裡的元素，當做字串那樣用regular找，如下

WHERE JsonBColumn -> 'many' {Something} '.*2$'

postgresql預設是不行的，於是有大大提供了很好的function，如下：

create or replace function jsonb_array_regex_like(json_array jsonb, pattern text)
returns boolean language sql as $$
    select bool_or(elem ~ pattern)
    from jsonb_array_elements_text(json_array) arr(elem)
$$;

把上面的內容丟到postgresql去query就行了，以後便可以直接使用jsonb_array_regex_like，用法如下

with the_data(id, jsonbcolumn) as (
    values
        (1, '{"single":"someText", "many": ["text1", "text2"]}'::jsonb)
    )
這個是先給一條測試資料

select * from the_data
where jsonb_array_regex_like(jsonbcolumn->'many', '^text.*');

jsonb_array_regex_like(jsonbcolumn->'many', '^text.*')就是用法了

jsonbcolumn是指josnb格式的欄位名稱，many是裡面的陣列，'^text.*'就是正規表現式了。注意這裡的正規表現式是比較嚴格的，'^text*'把「.」去掉就會出錯，因為會讀成「^tex」開頭，然後「t*」t有0個以上，這樣是不行的，一定要用「.」來表示萬用字元。