postgres對於json的支援度

Backend, Postgres
4 mins.
  1. 1. 緣由
  2. 2. 解決方法
    1. 2.1. 資料轉型
    2. 2.2. 取值
  3. 3. 結論
  4. 4. 參考
Postgres

data team的同事回報了一個bug，是我寫入的資料格式不正確，導致他們在解析資料上的有困難，先把寫入資料的問題解決以後，要幫忙把錯誤的資料導正回來，才發現postgres對於json的支援度蠻高

緣由

先來說說這種資料格式怎麼產生的，我們有一個academy的網站，app會用web view的方式開啟，為了要追蹤這是由哪邊連近來的，所以app在開網頁的時候會加上query parameter給我們，並且我們將這些資訊寫入到db作為觀看記錄

格式大概會是這樣 ?region=tw&platform=android，但是就出現了這種情況?region=tw&platform=android&region=tw&patform=android，收到了雙份的參數，導致寫入的時候變成了錯誤的格式

寫入的資料變成這樣

region platform
正常 tw android
錯誤 {‘tw’,’tw’} {‘android’,’android’}

解決方法

這個資料格式第一眼看到，就是往json的方向去找，剛好查到postgres官方文件表示從9.3的版本以後就可以直接使用

因此就馬上很開心的使用了裡面的一個function叫做json_object_keys是用來拿到json物件的key值，沒想到收到錯誤是傳進去的值不對，馬上試著用另一個看看to_json，沒想到一樣收到錯誤說這個資料格式不正確

原來這個根本不是json物件，而是一個不知道什麼的資料格式，那該怎麼辦呢

資料轉型

幸好，還有另一種轉型的function叫做array_to_json(anyarray [, pretty_bool])，我們可以這樣使用

1
2
select array_to_json(region::text[]) from table1
-- ['tw', 'tw']

並且得到一個正確的json陣列，接著就能根據這樣的資料透過json function做解析

取值

在postgres有提供一種operator的寫法，來取得json物件的資料

1
2
3
4
select '{"tw":123}'::json->'tw'
-- 123
select '["tw", 123]'::json->0
-- "tw"

那我們就來試試看搭配我們的資料，沒錯啦，可以完美的拿到資料，但有沒有發現一個小缺點資料是'tw'帶著單引號的

1
2
select array_to_json(region::text[])::json->0 from table1
-- 'tw'

這時候要用另一個方法來拿到只有tw的資料，使用>>，真是太完美了！

1
2
select array_to_json(region::text[])::json->>0 from table1
-- tw

結論

在postgres的json相關功能提供的蠻齊全，並且也有支援json作為欄位格式，在使用上就更加的彈性

這次的意外順便學到了一個新的東西，順便碰一下很久沒有碰到的資料庫，趕快把技能點上來XD

參考

prev： next：