HackMD
  • Beta
    Beta  Get a sneak peek of HackMD’s new design
    Turn on the feature preview and give us feedback.
    Go → Got it
    • Beta  Get a sneak peek of HackMD’s new design
      Beta  Get a sneak peek of HackMD’s new design
      Turn on the feature preview and give us feedback.
      Go → Got it
      • Sharing Link copied
      • /edit
      • View mode
        • Edit mode
        • View mode
        • Book mode
        • Slide mode
        Edit mode View mode Book mode Slide mode
      • Note Permission
      • Read
        • Owners
        • Signed-in users
        • Everyone
        Owners Signed-in users Everyone
      • Write
        • Owners
        • Signed-in users
        • Everyone
        Owners Signed-in users Everyone
      • More (Comment, Invitee)
      • Publishing
        Please check the box to agree to the Community Guidelines.
        Everyone on the web can find and read all notes of this public team.
        After the note is published, everyone on the web can find and read this note.
        See all published notes on profile page.
      • Commenting Enable
        Disabled Forbidden Owners Signed-in users Everyone
      • Permission
        • Forbidden
        • Owners
        • Signed-in users
        • Everyone
      • Invitee
      • No invitee
      • Options
      • Versions and GitHub Sync
      • Transfer ownership
      • Delete this note
      • Template
      • Insert from template
      • Export
      • Dropbox
      • Google Drive Export to Google Drive
      • Gist
      • Import
      • Dropbox
      • Google Drive Import from Google Drive
      • Gist
      • Clipboard
      • Download
      • Markdown
      • HTML
      • Raw HTML
    Menu Sharing Help
    Menu
    Options
    Versions and GitHub Sync Transfer ownership Delete this note
    Export
    Dropbox Google Drive Export to Google Drive Gist
    Import
    Dropbox Google Drive Import from Google Drive Gist Clipboard
    Download
    Markdown HTML Raw HTML
    Back
    Sharing
    Sharing Link copied
    /edit
    View mode
    • Edit mode
    • View mode
    • Book mode
    • Slide mode
    Edit mode View mode Book mode Slide mode
    Note Permission
    Read
    Owners
    • Owners
    • Signed-in users
    • Everyone
    Owners Signed-in users Everyone
    Write
    Owners
    • Owners
    • Signed-in users
    • Everyone
    Owners Signed-in users Everyone
    More (Comment, Invitee)
    Publishing
    Please check the box to agree to the Community Guidelines.
    Everyone on the web can find and read all notes of this public team.
    After the note is published, everyone on the web can find and read this note.
    See all published notes on profile page.
    More (Comment, Invitee)
    Commenting Enable
    Disabled Forbidden Owners Signed-in users Everyone
    Permission
    Owners
    • Forbidden
    • Owners
    • Signed-in users
    • Everyone
    Invitee
    No invitee
       owned this note    owned this note      
    Published Linked with GitHub
    Like BookmarkBookmarked
    Subscribed
    • Any changes
      Be notified of any changes
    • Mention me
      Be notified of mention me
    • Unsubscribe
    Subscribe
    JSON in PostgreSQL,建議跟不建議 - Rubin === ###### tags: `COSCUP2020` `入門` `TR213` {%hackmd 7CD4pdKQTa2p6Y3VqH3VrQ %} > 請從這裡開始 ## 正規化 * 正規化的目的是讓資料保持一致,不要讓資料重複 * 範例1: * 同一個會員資料表同時有電話1、電話2、電話3...,**重複屬性的欄位**在同一張資料表上。導致開發時修改、維護資料的困難。 * 並非每一個會員都有多支手機,或是固定N支手機。預先宣告N個手機欄位導致只有一支手機的用戶其餘手機都是null、或是程式維護不好導致手機1~手機8都是null,真正的手機出現在手機9... * 也不能假設使用者最多就是N支手機,未來遇到N+1手機的用戶,這個系統就等於限制了使用者的資料(可以把手機換成其他項目,像是購物車內的商品項目!),未來擴充也成為隱憂 * 總結,一個資料表上有重複的項目會導致(1)資料數量被限制 (2)冗余不必要的欄位出現導致資料庫效能拖慢 (3)程式端難以維護,需要知道手機的索引號才能寫出對應的SQL語句 * 範例2: * 商品名稱、商品描述等資訊,同時出現在商品資料表與訂單記錄資料表中。導致資料的一致性維護困難。 * 當產品A要修改名稱時,需要把訂單記錄資料表中的產品A也一併修改。會導致 (1)資料量大時會導致修改困難 (2)Lock資料表影響線上交易 (3)修改不完整導致資料錯誤(少修改到、where條件沒寫好) * 因此一般會建議**在交易紀錄資料表中記錄商品代號(pid),透過join的方式跟商品資料表連動、取得商品資料**,這樣要修改商品描述、商品名稱時,只需要修改商品資料表的內容即可,省下了大量IO、確保了資料的完整、一致性(~~如果搭配外鍵會更舒服~~),降低開發者的負擔。 * 反正規化 * query 時減少 join 的動作,把資料通通塞在同一列 * 報表性質的資料表會需要,有一些巨量的表格跟表格JOIN會爆炸 ## JSON support * PG12之後可支援 JSON 格式 * json: 純文字(含空白、換行符號等) * jsonb: 二進位轉換(無空白、換行,精簡) * 函數/運算子 * 若 json 格式不標準,無法 insert into pgdb(詳見講者範例) * json vs jsonb * <https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql> * 如果僅在應用程序中使用JSON表示形式,則PostgreSQL僅用於存儲和檢索此表示形式,應使用json。 * 如果您在PostgreSQL中對JSON值進行了大量操作,或對某些JSON字段使用索引,則應使用jsonb。 ### 一些函數 * `jsonb_pretty` * 可以把純文字的 json 轉為比較漂亮易讀的格式 * `select ["a", "b", "c", "d"]::json->2` * return "c" * 可判斷 key, value 是否存在 json array 當中,也支援 operator (`|, &`) * 可增刪 new key into current json * jsonb 可增加 index ## 問題與責任 * 要想清楚 Access pattern ,考慮商業邏輯、是否常 IO - 關心資料處理流程 - 關心資料粒度、交易流程 ## 議程現場實驗腳本 ``` SQL -- Rubin.Sheu 202/08/02 於COSCUP X PostgreSQL.tw -- 議程簡報與實驗腳本皆會提供在PostgreSQL.tw Facebook社群 -- 基本json/jsonb的呈現 -- Lab 1 基本json/jsonb比較 -- 建立json基本格式資料表 drop table if exists json_tab; create table json_tab( my_data json ); -- 建立jsonb基本格式資料表 drop table if exists jsonb_tab; create table jsonb_tab( my_data jsonb ); -- 嘗試插入json資料(純數字) insert into json_tab(my_data) values ('123'); -- 嘗試插入json資料(陣列,包含數值、文字、布林) insert into json_tab(my_data) values ('[100,"lulu",true]'); -- 嘗試插入非標準json資料 -- 非key-value insert into jsontab(my_data) values('{"abc"}'); -- 缺乏、不完整的json文字 insert into jsontab(my_data) values('{"abc":"non-json"'); -- 查詢輸入的結果 select * from json_tab; -- json 與 jsonb空間的不同 SELECT pg_column_size('{"test":"data"}'::json) union SELECT pg_column_size('{"test":"data"}'::jsonb); -- 清空json格式表格 truncate table json_tab; -- 針對json/jsonb輸入相同資料 insert into json_tab(my_data) values (' { "oid":"AZ10925786", "odt":"2020/01/01", "otime":"17:20:13", "cid":160844897, "order_list": [ { "pid":"B10294565", "sub_pid":"A00098", "pcount":2, "pprice":400 }, { "pid":"B10294887", "pcound":1, "pprice":1000 } ], "order_ext": [ { "ext_type":"smemo", "ext_info":"包含易碎物" }, { "ext_type":"smemo", "ext_info":"包含常溫品" }, { "ext_type":"umemo", "ext_info":"請晚上六點後再送貨謝謝" } ] } '); insert into jsonb_tab(my_data) values (' { "oid":"AZ10925786", "odt":"2020/01/01", "otime":"17:20:13", "cid":160844897, "order_list": [ { "pid":"B10294565", "sub_pid":"A00098", "pcount":2, "pprice":400 }, { "pid":"B10294887", "pcound":1, "pprice":1000 } ], "order_ext": [ { "ext_type":"smemo", "ext_info":"包含易碎物" }, { "ext_type":"smemo", "ext_info":"包含常溫品" }, { "ext_type":"umemo", "ext_info":"請晚上六點後再送貨謝謝" } ] } '); -- 顯示出的結果 select my_data from json_tab; select my_data from jsonb_tab; -- Lab2 對json資料的操作 -- 存取 -- jsonb_pretty讓輸出美化/結構化 select ' { "test":"this is json data" } '::json; select jsonb_pretty( ' { "test":"this is json data" } ' ); -- 用->操作指定資料 select '["a", "b", "c", "d"]'::json->2; -- 從零開始 select '["a", "b", "c", "d"]'::json->87; -- 不存在,回傳null -- 判斷 -- 有無包含key/value select '{"a":1, "b":2, "c":3}'::json @> '{"a":1}'::json; -- 用json無法正確存取 select '{"a":1, "b":2, "c":3}'::jsonb @> '{"a":1}'::jsonb; -- 用jsonb即可正確存取 select '{"a":1, "b":2, "c":3}'::jsonb @> '{"a":13}'::jsonb; -- 用jsonb即可正確存取 -- 有無包含key select '{"a":1, "b":2, "c":3}'::jsonb ? 'a'; -- 包含該key select '{"a":1, "b":2, "c":3}'::jsonb ? 'z'; -- 不包含該key -- 有無包含key(or判斷) select '{"a":1, "b":2, "c":3}'::jsonb ?| array['a','z']; -- 包含該key集合 select '{"a":1, "b":2, "c":3}'::jsonb ?| array['y','z']; -- 包含該key集合 -- 有無包含key(and判斷) select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a','b']; -- 包含該key集合 select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a','z']; -- 包含該key集合 -- 是否包含該path select '{"a":1, "b":2, "c":3}'::jsonb @? '$.a'; -- 包含該key集合 select '{"a":1, "b":2, "c":3}'::jsonb @? '$.f'; -- 包含該key集合 -- 操作 -- 串接value select '[1,2,3,4]'::jsonb || '[5,6]'::jsonb; -- 移除指定索引項目 select '[1,2,3,4]'::jsonb - 0 ; -- 移除指定key select '{"a":1, "b":2, "c":3}'::jsonb - 'a' ; -- jsonpath操作 /* { "oid":"AZ10925786", "odt":"2020/01/01", "otime":"17:20:13", "cid":160844897, "order_list": [ { "pid":"B10294565", "sub_pid":"A00098", "pcount":2, "pprice":400 }, { "pid":"B10294887", "pcound":1, "pprice":1000 } ], "order_ext": [ { "ext_type":"smemo", "ext_info":"包含易碎物" }, { "ext_type":"smemo", "ext_info":"包含常溫品" }, { "ext_type":"umemo", "ext_info":"請晚上六點後再送貨謝謝" } ] }*/ select my_data from jsonb_tab; -- 查詢全部資料 -- 單獨取出oid select jsonb_path_query(my_data,'$.oid') from jsonb_tab; -- 單獨取出order_list內的資料 select jsonb_path_query(my_data,'$.order_list') from jsonb_tab; -- 單獨取出第一筆order_list的pid select jsonb_path_query(my_data,'$.order_list[0].pid') from jsonb_tab; -- 實驗結束,銷毀資料表 drop table if exists jsonb_tab; drop table if exists json_tab; ``` ## 參考資料 [JSON Functions and Operators](https://www.postgresql.org/docs/9.5/functions-json.html) [JSON Types](https://www.postgresql.org/docs/9.4/datatype-json.html) [PostgreSQL internals: JSONB type and its indexes](https://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes/)

    Import from clipboard

    Advanced permission required

    Your current role can only read. Ask the system administrator to acquire write and comment permission.

    This team is disabled

    Sorry, this team is disabled. You can't edit this note.

    This note is locked

    Sorry, only owner can edit this note.

    Reach the limit

    Sorry, you've reached the max length this note can be.
    Please reduce the content or divide it to more notes, thank you!

    Import from Gist

    Import from Snippet

    or

    Export to Snippet

    Are you sure?

    Do you really want to delete this note?
    All users will lost their connection.

    Create a note from template

    Create a note from template

    Oops...
    This template is not available.


    Upgrade

    All
    • All
    • Team
    No template found.

    Create custom template


    Upgrade

    Delete template

    Do you really want to delete this template?

    This page need refresh

    You have an incompatible client version.
    Refresh to update.
    New version available!
    See releases notes here
    Refresh to enjoy new features.
    Your user state has changed.
    Refresh to load new user state.

    Sign in

    Forgot password

    or

    By clicking below, you agree to our terms of service.

    Sign in via Facebook Sign in via Twitter Sign in via GitHub Sign in via Dropbox

    New to HackMD? Sign up

    Help

    • English
    • 中文
    • Français
    • Deutsch
    • 日本語
    • Español
    • Català
    • Ελληνικά
    • Português
    • italiano
    • Türkçe
    • Русский
    • Nederlands
    • hrvatski jezik
    • język polski
    • Українська
    • हिन्दी
    • svenska
    • Esperanto
    • dansk

    Documents

    Tutorials

    Book Mode Tutorial

    Slide Mode Tutorial

    YAML Metadata

    Contacts

    Facebook

    Twitter

    Feedback

    Send us email

    Resources

    Releases

    Pricing

    Blog

    Policy

    Terms

    Privacy

    Cheatsheet

    Syntax Example Reference
    # Header Header 基本排版
    - Unordered List
    • Unordered List
    1. Ordered List
    1. Ordered List
    - [ ] Todo List
    • Todo List
    > Blockquote
    Blockquote
    **Bold font** Bold font
    *Italics font* Italics font
    ~~Strikethrough~~ Strikethrough
    19^th^ 19th
    H~2~O H2O
    ++Inserted text++ Inserted text
    ==Marked text== Marked text
    [link text](https:// "title") Link
    ![image alt](https:// "title") Image
    `Code` Code 在筆記中貼入程式碼
    ```javascript
    var i = 0;
    ```
    var i = 0;
    :smile: :smile: Emoji list
    {%youtube youtube_id %} Externals
    $L^aT_eX$ LaTeX
    :::info
    This is a alert area.
    :::

    This is a alert area.

    Versions

    Versions and GitHub Sync

    Sign in to link this note to GitHub Learn more
    This note is not linked with GitHub Learn more
     
    Add badge Pull Push GitHub Link Settings
    Upgrade now

    Version named by    

    More Less
    • Edit
    • Delete

    Note content is identical to the latest version.
    Compare with
      Choose a version
      No search result
      Version not found

    Feedback

    Submission failed, please try again

    Thanks for your support.

    On a scale of 0-10, how likely is it that you would recommend HackMD to your friends, family or business associates?

    Please give us some advice and help us improve HackMD.

     

    Thanks for your feedback

    Remove version name

    Do you want to remove this version name and description?

    Transfer ownership

    Transfer to
      Warning: is a public team. If you transfer note to this team, everyone on the web can find and read this note.

        Link with GitHub

        Please authorize HackMD on GitHub

        Please sign in to GitHub and install the HackMD app on your GitHub repo. Learn more

         Sign in to GitHub

        HackMD links with GitHub through a GitHub App. You can choose which repo to install our App.

        Push the note to GitHub Push to GitHub Pull a file from GitHub

          Authorize again
         

        Choose which file to push to

        Select repo
        Refresh Authorize more repos
        Select branch
        Select file
        Select branch
        Choose version(s) to push
        • Save a new version and push
        • Choose from existing versions
        Available push count

        Upgrade

        Pull from GitHub

         
        File from GitHub
        File from HackMD

        GitHub Link Settings

        File linked

        Linked by
        File path
        Last synced branch
        Available push count

        Upgrade

        Danger Zone

        Unlink
        You will no longer receive notification when GitHub file changes after unlink.

        Syncing

        Push failed

        Push successfully