進階 Postgres JSON 查詢技巧

NoSQL 入門

Jake Roach

Data Engineer

查詢巢狀 JSON 資料

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

讓巢狀資料查詢更容易：

#> 和 #>> 運算子
json_extract_path 與 json_extract_path_text 函式

#> 與 #>> 運算子

兩個資料表：左為文件資料，右為使用井字箭頭與雙井字箭頭運算子自文件中擷取出的多個欄位。

#> 運算子

作用在欄位上，接受字串陣列
路徑不存在時回傳 null
#>> 以文字回傳欄位

SELECT
    parent_meta #> '{jobs}' AS jobs,
    parent_meta #> '{jobs, P1}' AS jobs_P1,
    parent_meta #> '{jobs, income}' AS income,
    parent_meta #>> '{jobs, P2}' AS jobs_P2
FROM student;

json_extract_path 與 json_extract_path_text

json_extract_path

提供欄位與任意數目的路徑節點
路徑不存在時回傳 null
json_extract_path_text

SELECT
  json_extract_path(parent_meta, 'jobs') AS jobs,
  json_extract_path(parent_meta, 'jobs', 'P1') AS jobs_P1,
  json_extract_path(parent_meta, 'jobs', 'income') AS income,
  json_extract_path_text(parent_meta, 'jobs', 'P2') AS jobs_P2,
FROM student;

兩個資料表：左為文件資料，右為使用 json_extract_path 與 json_extract_path_text 函式自文件中擷取出的多個欄位。

複習

SELECT
    <column-name> #> '{<field-name>}' AS <alias>,
    <column-name> #> '{<field-name>, <field-name>}' AS <alias>,
    <column-name> #>> '{<field-name>, <field-name>}' AS <alias>
FROM <table-name>;

SELECT
  json_extract_path(<column-name>, '<field-name>') AS <alias>,
  json_extract_path(<column-name>, '<field-name>', '<field-name>') AS <alias>,
  json_extract_path_text(<column-name>, '<field-name>', '<field-name>') AS <alias>
FROM <table-name>;

一起來練習吧！

NoSQL 入門