使用 Postgres 查詢 JSON 資料

NoSQL 入門

Jake Roach

Data Engineer

用 Postgres 查詢 JSON 資料

Two tables, one containing a single column of type JSON, and the other with the result of the query extract information from the parent_meta column.

-> 運算子

傳入鍵，回傳欄位為 JSON

->> 運算子

傳入鍵，回傳欄位為文字

SELECT
    parent_meta -> 'guardian' AS guardian
    parent_meta ->> 'status' AS status
FROM student;

查詢巢狀 JSON 物件

兩個資料表：一個只有 JSON 型別的單一欄位，另一個是從 parent_meta 欄位查詢並抽取資訊後的結果。

查詢巢狀 JSON 物件：

搭配使用 -> 與 ->>
先回傳巢狀物件
再抽取欄位

SELECT
    parent_meta -> 'jobs' ->> 'P1' AS jobs_P1,
    parent_meta -> 'jobs' ->> 'P2' AS jobs_P2
FROM student;

查詢 JSON 陣列

兩個資料表：一個只有 JSON 型別的單一欄位，另一個是從 parent_meta 欄位查詢並抽取資訊後的結果。

存取 JSON 陣列元素：

對 -> 傳入 INT，回傳為 JSON
對 ->> 傳入 INT，回傳為文字

SELECT
    parent_meta -> 'educations' ->> 0
    parent_meta -> 'educations' ->> 1
FROM student;

辨識 JSON 物件中資料的型別

兩個資料表：一個只有 JSON 型別的單一欄位，另一個只有一欄輸出為 JSON 物件外層型別。

json_typeof 函式

回傳最外層物件的型別
與 -> 搭配使用
有助於除錯與撰寫查詢
通常不與 ->> 搭配

SELECT
    json_typeof(parent_meta -> 'jobs')
FROM students;

重點回顧

SELECT
    -- 最外層欄位
    <column-name> -> '<field-name>' AS <alias>,
    <column-name> ->> '<field-name>' AS <alias>,

    -- 巢狀欄位
    <column-name> -> '<parent-field-name>' ->> '<nested-field-name>' AS <alias>,

    -- 陣列
    <column-name> -> '<parent-field-name>' -> 0 AS <alias>,
    <column-name> -> '<parent-field-name>' ->> 1 AS <alias>,

    -- 型別查詢
    json_typeof(<column-name> -> <field-name>) AS <alias>
FROM <table-name>;

一起來練習吧！

NoSQL 入門