sql截取字符串這么奇怪的需求？
原因是我們的pr和master build每次運(yùn)行都會(huì)產(chǎn)生大量的數(shù)據(jù)。我們將這部分?jǐn)?shù)據(jù)存在關(guān)系型數(shù)據(jù)庫(kù)里。為了日后方便進(jìn)行統(tǒng)計(jì)和甄別pr的質(zhì)量，是否引入regression。
由于pr采用的是webhook的方式，定制化了各種觸發(fā)的參數(shù)，比如：run,run-ui, build等等。一旦用戶輸入了這幾種comment，jenkins就會(huì)自動(dòng)運(yùn)行構(gòu)建腳本。與此同時(shí)，我們也將用戶的行為記錄下來(lái)。
研究過(guò)github api的人一定知道，api中攜帶了大量的信息，包括pr的owner，組織，reviewer等等。雖然是json格式的。我們存儲(chǔ)的時(shí)候，還是一股腦的存的text。一方面是快捷，而且不需要過(guò)多的考慮占用的字段長(zhǎng)度。雖然存儲(chǔ)的時(shí)候開心了，但是當(dāng)我們想把其中的數(shù)據(jù)抽取出來(lái)的時(shí)候Orz，太難受了。巨長(zhǎng)的json串還是array類型的。
沒(méi)有辦法，無(wú)法使用postgres自帶的解析json串的函數(shù)，具體可參考http://www.postgres.cn/docs/12/datatype-json.html
我們的問(wèn)題在于，怎樣從一堆巨長(zhǎng)的json串中，摳出我們想要的comment。沒(méi)辦法，截取字符串吧。

position 函數(shù)，SELECT POSITION('Tutorial' IN 'GeeksForGeeks Tutorial');

簡(jiǎn)單用法就是在字符串里找出我需要截取的值。 類似于如下

image.png

我的json由于比較大，且是一個(gè)數(shù)組串，根據(jù)一般的key來(lái)獲取值，并不能達(dá)到要求。因?yàn)閗ey可能都是一樣的name。
所以我們通過(guò)識(shí)別性比較大的值：ghprbCommentBody，其中一部分信息是這樣的：

{"name":"ghprbCommentBody","value":"run only-build"},

且value的值是動(dòng)態(tài)變化的。
因此可以通過(guò)position來(lái)截取。首先定位到ghprbCommentBody，

            POSITION (
            'ghprbCommentBody' IN b."parameters"
            ) FOR 50

取出長(zhǎng)度為50。
然后拿到了小部分的值，再在這個(gè)基礎(chǔ)上進(jìn)一步截取，

image.png

會(huì)發(fā)現(xiàn)結(jié)果變成這樣

image.png

接下來(lái)就需要對(duì)子串進(jìn)行截取，去除引號(hào)，以及不相關(guān)的鍵值。

TRIM (
        TRAILING '"'
        FROM
            TRIM (
                LEADING ':"'
                FROM
                    TRIM (
                        LEADING '"ghprbCommentBody","value"'
                        FROM
                            SUBSTRING (
                                b."parameters"
                                FROM
                                    POSITION (
                                        '"ghprbCommentBody' IN b."parameters"
                                    ) FOR POSITION (
                                        '}' IN SUBSTRING (
                                            b."parameters"
                                            FROM
                                                POSITION (
                                                    'ghprbCommentBody' IN b."parameters"
                                                ) FOR 50
                                        )
                                    )
                            )
                    )
            )
    ) AS "parameters"

LEADING 表示從頭部開始。這樣就達(dá)到了我們的預(yù)期

image.png