-> 與 ->> 查詢差異

PostgreSQL 查詢 field 內容時有兩大 operators，主要差別在於回傳的資料型別
->>: 回傳的是 Json 的字面量
->: 回傳的是 Jsonb 物件

內容查詢語法: ->> Operator

• ->> 用來查出 JSON/JSONB 物件 field 內容，並以 String 格式 回傳結果。
• 注意 PostgreSQL ->> 的查詢結果顯示時 沒有 用 Double Quotes 包住。

  select address->>'country' as COUNTRY FROM profile;
  
COUNTRY
--------
TAIWAN
KOREA
JAPAN
CHINA

Jsonb 物件查詢語法: -> Operator

• -> 用來查出 JSON/JSONB 物件內容，並以 JSON/JSONB 物件格式 回傳結果。
• -> JSON 查詢，所得為 postgreSQL JSON 物件，所以可以 -> 繼續往內槽串查。最終再以 ->> 取值。
• 注意 PostgreSQL -> 的查詢結果 有 用 Double Quotes (為 JSON 物件)包住。

"student": {
	"first_name": "insect",
	"last_name": "totem",
	"class": {
		"class_name" : "A",
		"grade" : {
			"grade_name" : "3",
			"school" :{
				"school_name" : "Entomology"
			}
		}
	}
}

• ->> return String

select Student_column->'student'->'class'->'grade'->'school'->> 'school_name' as SCHOOL from Profile;

SCHOOL
text
-------
Entomology

• -> return jsonb object

select Student_column->'student'->'class'->'grade'->'school'-> 'school_name' as SCHOOL from Profile;


SCHOOL
jsonb
-------
"Entomology"

• -> return jsonb object

select Student_column->'student'->'class'->'grade'->'school'  as SCHOOL from Profile;


SCHOOL
jsonb
-------
"{"school_name": "Entomology"}"

當查詢結果是 Array Element

• 當查詢結果是 Array Element 時，PostgreSQL 可以再向內以 index 查找指定位置元素。
  • 註: zero-based index
  • ->>、-> 若指向的結果是 Array，則允許再串一個 zero-based index，以取出單一元素。
• 例如: 'profile'->'phones' -> 0

範例情境

---Profile
{
    "profile": {
                "first_name": "insect",
                "last_name": "totem",
                "phones": ["0922-222-222","0955-555-555"]
            }
}

    SELECT last_name, raw_data -> 'profile'->'phones'->0  as phone_1 FROM Student
      
    LAST_NAMR               PHONE_1  
    character varying       jsonb    
    ----------------------------------------
    totem                   "0922-222-222"  

Json 查詢 Operators 變體

• 與 jsonb_set() 改資料指定路徑時類似，PostgreSQL Jsonb 內建其他路徑簡易寫法的語法糖(Syntactic sugar)。
• 註 function 可以經由 parse arg 來得知 Path Style，Operators 用在 SQL command 中需以其他方式辨別。

查詢相關 operators 摘要

• 在查詢相關 operators 中，
  • # 代表後方採用 Json Object Style path
  • > 單個代表 Jsonb 物件向下再探一層。
  • >> 兩個代表將 Jsonb 物件解析成 String。

operator	path style(語法糖)
->	連續符號串接 Style Path
->>	連續符號串接 Style Path
#>	Json Object Style path
#>>	Json Object Style path

以 Json Object 取代路徑

• 情境

---Specimen
{
        "Class": "Insecta",
        "Genus": {
            "name": "Aedes",
            "Subgenus": {
                "name": "Stegomyia"
            }
        },
        "Order": "Diptera",
        "Family": "Culicidae",
        "Phylum": "Arthropoda",
        "Kingdom": "Animalia",
        "Species": "albopictus"
}

• 範例

    
    -- original 
    select insect.classification -> 'Genus' -> 'Subgenus' -> 'name' sample_name  from insect_specimen insect;
    --Jsonb Object style
    select insect.classification #> '{"Genus","Subgenus","name" }' sample_name  from insect_specimen insect;

    === with index    
    select last_name, raw_data -> 'profile'->'phones'->0  as phone_1 FROM Student
    ===Jsonb Object style. index 為數值故沒 double quotes
    select last_name, raw_data #> '{"profile","phones",0}' as phone_1 FROM Student