處理和建立JSON資料

json和jsonb操作符表展示了可以用於JSON資料類型的操作符。 此外，比較操作符表所示的常用比較操作符也適用於 jsonb ，但不適用於 json 。 比較操作符遵循jsonb索引中的B樹操作概要的定序。

json 和 jsonb 操作符

json -> integer → json jsonb -> integer → jsonb 提取JSON數組的第 n 個元素（數組元素從0開始索引，但負整數從末尾開始計數）。 '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2 → {"c":"baz"} '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3 → {"a":"foo"}

json -> text → json jsonb -> text → jsonb 用給定的鍵提取JSON對象欄位。 '{"a": {"b":"foo"}}'::json -> 'a' → {"b":"foo"}

json ->> integer → text jsonb ->> integer → text 提取JSON數組的第 n 個元素，作為 text 。 '[1,2,3]'::json ->> 2 → 3

json ->> text → text jsonb ->> text → text 用給定的鍵提取JSON對象欄位，作為 text 。 '{"a":1,"b":2}'::json ->> 'b' → 2

json #> text[] → json jsonb #> text[] → jsonb 提取指定路徑下的JSON子物件，路徑元素可以是欄位鍵或數組索引。 '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}' → "bar"

json #>> text[] → text jsonb #>> text[] → text 將指定路徑上的JSON子物件提取為 text 。 '{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}' → bar

還有一些操作符僅適用於 jsonb ，如表附加的jsonb操作符所示。 附加的 jsonb 操作符

jsonb @> jsonb → boolean 第一個JSON值是否包含第二個? '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb → t

jsonb <@ jsonb → boolean 第二個JSON中是否包含第一個JSON值? '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb → t

jsonb ? text → boolean 文本字串是否作為JSON值中的頂級鍵或數組元素存在? '{"a":1, "b":2}'::jsonb ? 'b' → t '["a", "b", "c"]'::jsonb ? 'b' → t

jsonb ?| text[] → boolean 文本數組中的字串是否作為頂級鍵或數組元素存在? '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

jsonb ?& text[] → boolean 文本數組中的所有字串都作為頂級鍵或數組元素存在嗎? '["a", "b", "c"]'::jsonb ?& array['a', 'b'] → t

jsonb || jsonb → jsonb 串連兩個 jsonb 值。串連兩個數組將產生一個包含每個輸入的所有元素的數組。串連兩個對象將產生一個包含它們鍵的並集的對象，當存在重複的鍵時取第二個對象的值。 所有其他情況都是通過將非數組輸入轉換為單個元素數組，然後按照兩個數組的方式進行處理。 不遞迴操作：只有頂級數組或對象結構被合并。 '["a", "b"]'::jsonb || '["a", "d"]'::jsonb → ["a", "b", "a", "d"] '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb → {"a": "b", "c": "d"}

jsonb - text → jsonb 從JSON對象中刪除鍵（以及它的值），或從JSON數組中刪除匹配的字串值。 '{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"} '["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]

jsonb - text[] → jsonb 從左運算元中刪除所有匹配的鍵或數組元素。 '{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}

jsonb - integer → jsonb 刪除具有指定索引的數組元素（負整數從末尾計數）。如果JSON值不是數組，則拋出錯誤。 '["a", "b"]'::jsonb - 1 → ["a"]

jsonb #- text[] → jsonb 刪除指定路徑上的欄位或數組元素，路徑元素可以是欄位鍵或數組索引。 '["a", {"b":1}]'::jsonb #- '{1,b}' → ["a", {}]

jsonb @? jsonpath → boolean JSON路徑是否為指定的JSON值返回任何項? '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' → t

jsonb @@ jsonpath → boolean 返回指定JSON值的JSON路徑謂詞檢查的結果。只考慮結果的第一項。如果結果不是布爾值，則返回 NULL 。 '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t

JSON建立函數表顯示可用於構造 json 和 jsonb 值的函數。

JSON建立函數

1. 將任何SQL值轉換為json或jsonb。數組和組合遞迴地轉換為數組和對象（多維陣列在JSON中變成數組的數組）。 否則，如果存在從SQL資料類型到json的類型轉換，則造型函數將用於執行轉換; [a] 否則，將產生一個標量json值。對於除數字、布爾值或空值之外的任何標量，將使用文本表示，並根據需要進行轉義，使其成為有效JSON字串值。

to_json ( anyelement ) → json
to_jsonb ( anyelement ) → jsonb
to_json('Fred said "Hi."'::text) → "Fred said \"Hi.\""
to_jsonb(row(42, 'Fred said "Hi."'::text)) → {"f1": 42, "f2": "Fred said \"Hi.\""}

2. 將SQL數群組轉換為JSON數組。該行為與to_json相同，只是如果可選boolean參數為真，分行符號將在頂級數組元素之間添加。

array_to_json ( anyarray [, boolean ] ) → json
array_to_json('{{1,5},{99,100}}'::int[]) → [[1,5],[99,100]]

3. 將SQL組合值轉換為JSON對象。該行為與to_json相同，只是如果可選boolean參數為真，分行符號將在頂級元素之間添加。

row_to_json ( record [, boolean ] ) → json
row_to_json(row(1,'foo')) → {"f1":1,"f2":"foo"}

4. 根據可變參數列表構建可能異構類型的JSON數組。每個參數都按照to_json或to_jsonb進行轉換。

json_build_array ( VARIADIC "any" ) → json
jsonb_build_array ( VARIADIC "any" ) → jsonb
json_build_array(1, 2, 'foo', 4, 5) → [1, 2, "foo", 4, 5]

5. 根據可變參數列表構建一個 JSON 對象。按照慣例，參數列表由交替的鍵和值組成。 關鍵參數強制轉換為文本;值參數按照 to_json 或 to_jsonb 進行轉換。

json_build_object ( VARIADIC "any" ) → json
jsonb_build_object ( VARIADIC "any" ) → jsonb
json_build_object('foo', 1, 2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

6. 從文本數組構建 JSON 對象。該數組必須有兩個維度，一個維度成員數為偶數，在這種情況下，它們被視為交替的鍵/值對; 另一個維度成員數為二維，每個內部數組恰好有兩個元素，它們被視為鍵/值對。所有值都轉換為JSON 字串。

json_object ( text[] ) → json
jsonb_object ( text[] ) → jsonb
json_object('{a, 1, b, "def", c, 3.5}') → {"a" : "1", "b" : "def", "c" : "3.5"}
json_object('{{a, 1}, {b, "def"}, {c, 3.5}}') → {"a" : "1", "b" : "def", "c" : "3.5"}

7. 這種形式的json_object從單獨的文本數組中成對地擷取鍵和值。否則，它與單參數形式相同。

json_object ( keys text[], values text[] ) → json
jsonb_object ( keys text[], values text[] ) → jsonb
json_object('{a,b}', '{1,2}') → {"a": "1", "b": "2"}

[a] 例如，hstore擴充有一個從hstore到json的轉換，這樣通過json建立函數轉換的hstore值將被表示為json對象，而不是原始字串值。

JSON處理函數

1. 將頂級JSON數組展開為一組JSON值。

json_array_elements ( json ) → setof json
jsonb_array_elements ( jsonb ) → setof jsonb
select * from json_array_elements('[1,true, [2,false]]') →
   value
-----------
 [2,false]

2. 將頂級JSON數組展開為一組文本值。

json_array_elements_text ( json ) → setof text
jsonb_array_elements_text ( jsonb ) → setof text
select * from json_array_elements_text('["foo", "bar"]') →
   value
-----------
 bar

3. 返回頂級JSON數組中的元素數量。

json_array_length ( json ) → integer
jsonb_array_length ( jsonb ) → integer
json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') → 5

4. 將頂級JSON對象展開為一組鍵/值對。

json_each ( json ) → setof record ( key text, value json )
jsonb_each ( jsonb ) → setof record ( key text, value jsonb )
select * from json_each('{"a":"foo", "b":"bar"}') →
 key | value
-----+-------
 a   | "foo"
 b   | "bar"

5. 將頂級JSON對象擴充為一組鍵/值對。返回的值的類型為文本。

json_each_text ( json ) → setof record ( key text, value text )
jsonb_each_text ( jsonb ) → setof record ( key text, value text )
select * from json_each_text('{"a":"foo", "b":"bar"}') →
 key | value
-----+-------
 a   | foo
 b   | bar

6. 在指定路徑下提取JSON子物件。這在功能上相當於#>操作符，但在某些情況下，將路徑寫成可變參數列表會更方便。

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb
json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"

7. 將指定路徑上的JSON子物件提取為文本。這在功能上等同於#>>操作符。

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text
json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → foo

8. 返回頂級JSON對象中的鍵集合。

json_object_keys ( json ) → setof text
jsonb_object_keys ( jsonb ) → setof text
select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') →
 json_object_keys
------------------
 f2

9. 將頂級JSON對象擴充為具有基本參數的複合類型的行。JSON對象將被掃描，尋找名稱與輸出行類型的列名匹配的欄位，並將它們的值插入到輸出的這些列中。（不對應任何輸出資料行名的欄位將被忽略。）在典型的使用中，基本的值僅為 NULL，這意味著任何不匹配任何對象欄位的輸出資料行都將被填充為空白。 但是，如果 base不為NULL，那麼它包含的值將用於不匹配的列。

要將JSON值轉換為輸出資料行的SQL類型，需要按次序應用以下規則：

在所有情況下，JSON空值都會轉換為SQL空值。

如果輸出資料行的類型是json或jsonb，則會精確地重製JSON值。

如果輸出資料行是複合（行）類型，且JSON值是JSON對象，則該對象的欄位將轉換為輸出行類型的列，通過這些規則的遞迴應用程式。

同樣，如果輸出資料行是數群組類型，而JSON值是JSON數組，則通過這些規則的遞迴應用程式將JSON數組的元素轉換為輸出數組的元素。

否則，如果JSON值是字串，則將字串的內容提供給輸入轉換函式，用以確定列的資料類型。

否則，JSON值的普通文本表示將被提供給輸入轉換函式，以確定列的資料類型。

雖然下面的樣本使用一個常量JSON值，典型的用法是在查詢的FROM子句中從另一個表側面地引用json或jsonb列。 在FROM子句中編寫json_populate_record是一種很好的實踐，因為提取的所有列都可以使用，而不需要重複的函數調用。

json_populate_record ( base anyelement, from_json json ) → anyelement
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement
create type subrowtype as (d int, e text); create type myrowtype as (a int, b text[], c subrowtype);
select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}, "x": "foo"}') →
 a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")

10. 將對象的頂級JSON數組展開為一組具有基本參數的複合類型的行。 對於json[b]_populate_record，將如上所述處理JSON數組的每個元素。

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement
create type twoints as (a int, b int);
select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]') →
 a | b
---+---
 1 | 2
 3 | 4

11. 將頂級JSON對象展開為具有由AS子句定義的複合類型的行。 （與所有返回record的函數一樣，調用查詢必須使用AS子句顯式定義記錄的結構。）輸出記錄由JSON對象的欄位填充，與上面描述的 json[b]_populate_record的方式相同。 由於沒有輸入記錄值，不匹配的列總是用空值填充。

json_to_record ( json ) → record
jsonb_to_record ( jsonb ) → record
create type myrowtype as (a int, b text);
select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype) →
 a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")

12. 將頂級JSON對象數組展開為一組由 AS 子句定義的複合類型的行。（與所有返回record的函數一樣，調用查詢必須使用AS子句顯式定義記錄的結構）對於json[b]_populate_record，將如上所述處理JSON數組的每個元素。

json_to_recordset ( json ) → setof record
jsonb_to_recordset ( jsonb ) → setof record
select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text) →
 a |  b
---+-----
 1 | foo
 2 |

12. 返回target，將path指定的項替換為new_value， 如果create_if_missing為真（此為預設值）並且path 指定的項不存在，則添加 new_value。 路徑中的所有前面步驟都必須存在，否則將不加改變地返回 target。 與面向路徑操作符一樣，負整數出現在 JSON 數組末尾的 path 計數中。 如果最後一個路徑步驟是超出範圍的數組索引，並且 create_if_missing 為真，那麼如果索引為負，新值將添加到數組的開頭，如果索引為正，則添加到數組的結尾。

jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) → jsonb
jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false) → [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]') → [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

13. 如果 new_value 不為 NULL，則行為與 jsonb_set 完全一樣。 否則，根據 null_value_treatment 的值，它必須是'raise_exception'，'use_json_null', 'delete_key', 或'return_target'。 預設值為'use_json_null'。

jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb
jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null) → [{"f1":null,"f2":null},2,null,3]
jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target') → [{"f1": 99, "f2": null}, 2]

14. 返回插入 new_value 的 target。 如果 path 指派的項是一個數組元素，如果 insert_after 為假（此為預設值），則 new_value 將被插入到該項之前，如果 insert_after 為真則在該項之後。 如果由 path 指派的項是一個對象欄位，則只在對象不包含該鍵時才插入 new_value。 路徑中的所有前面步驟都必須存在，否則將不加改變地返回 target。 與面向路徑操作符一樣，負整數出現在 JSON 數組末尾的 path 計數中。 如果最後一個路徑步驟是超出範圍的數組下標，則如果下標為負，則將新值添加到數組的開頭;如果下標為正，則將新值添加到數組的結尾。

jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after boolean ] ) → jsonb
jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"') → {"a": [0, "new_value", 1, 2]}
jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) → {"a": [0, 1, "new_value", 2]}

15. 從給定的 JSON 值中刪除所有具有空值的對象欄位，遞迴地。非對象欄位的空值是未受影響的。

json_strip_nulls ( json ) → json
jsonb_strip_nulls ( jsonb ) → jsonb
json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]') → [{"f1":1},2,null,3]

16. 檢查 JSON 路徑是否返回指定 JSON 值的任何項。如果指定了 vars 參數，則它必須是一個 JSON 對象，並且它的欄位提供要替換到 jsonpath 運算式中的名稱值。 如果指定了 silent 參數並為 true，函數會抑制與@? 和 @@運算子相同的錯誤。

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → t

17. 返回指定 JSON 值的 JSON 路徑謂詞檢查的結果。只有結果的第一項被考慮在內。 如果結果不是布爾值，則返回 NULL。可選的 vars 和 silent 參數的作用與 jsonb_path_exists 相同。

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ >= $min && @ <= $max))', '{"min":2, "max":4}') → t

18. 為指定的 JSON 值返回由 JSON 路徑返回的所有 JSON 項。可選的 vars 和 silent 參數的作用與 jsonb_path_exists 相同。

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb
select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') →
 jsonb_path_query
------------------
 4

19. 以 JSON 數組的形式返回由 JSON 路徑為指定的 JSON 值返回的所有 JSON 項。可選的 vars 和 silent 參數的作用與 jsonb_path_exists 相同。

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → [2, 3, 4]

20. 為指定的 JSON 值返回由 JSON 路徑返回的第一個 JSON 項。如果沒有結果則返回 NULL。 可選的 vars 和 silent 參數的作用與 jsonb_path_exists 相同。

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') → 2

21. 這些函數與上面描述的沒有_tz 尾碼的對應函數類似，除了這些函數支援需要時區感知轉換的日期/時間值比較之外。 下面的樣本需要將只包含日期的值 2015-08-02 解釋為帶有時區的時間戳記，因此結果依賴於當前 TimeZone 設定。 由於這種依賴性，這些函數被標記為穩定的，這意味著不能在索引中使用這些函數。 它們的對應項是不可改變的，因此可以用於索引;但是，如果要求他們進行這樣的比較，他們就會拋出錯誤。

jsonb_path_exists_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_match_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean
jsonb_path_query_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb
jsonb_path_query_array_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_query_first_tz ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb
jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t

22. 將給定的 JSON 值轉換為精美列印的，縮排的文本。

jsonb_pretty ( jsonb ) → text
jsonb_pretty('[{"f1":1,"f2":null}, 2]') →
        "f1": 1,
        "f2": null
]

23. 以文本字串形式返回頂級 JSON 值的類型。可能的類型有 object, array,string, number,boolean, 和 null。 (null 的結果不應該與 SQL NULL 混淆;參見樣本。

json_typeof ( json ) → text
jsonb_typeof ( jsonb ) → text
json_typeof('-123.4') → number
json_typeof('null'::json) → null
json_typeof(NULL::json) IS NULL → t

彙總函式 json_agg 將彙總記錄值為 JSON，彙總函式 json_object_agg 將彙總成對的值為 JSON 對象， 以及它們在 jsonb 中的相當的（函數）， jsonb_agg 和 jsonb_object_agg 。

SQL/JSON 路徑語言

SQL/JSON 路徑運算式指定了要從 JSON 資料中檢索的專案，類似於 SQL 訪問 XML 時使用的 XPath 運算式。 在本資料庫中，路徑運算式作為 jsonpath 資料類型實現，可以使用 jsonpath Type 中的任何元素。

JSON 查詢函數和操作符將提供的路徑運算式傳遞給 path engine 進行評估。 如果運算式與被查詢的 JSON 資料匹配，則返回相應的 JSON 項或項集。 路徑運算式是用 SQL/JSON 路徑語言編寫的，也可以包括算術運算式和函數。

路徑運算式由 jsonpath 資料類型允許的元素序列組成。路徑運算式通常從左向右求值，但你可以使用圓括弧來更改操作的順序。 如果計算成功，將產生一系列 JSON 項，並將計算結果返回到 JSON 查詢函數，該函數將完成指定的計算。

要引用正在查詢的 JSON 值（ context item 項），在路徑運算式中使用 $ 變數。 它後面可以跟著一個或多個 accessor operators，這些操作符在 JSON 結構中逐級向下檢索上下文項的子項。 後面的每個操作符處理前一個求值步驟的結果。

例如，假設你有一些你想要解析的來自 GPS 跟蹤器的 JSON 資料，例如：

    {
      "track": {
        "segments": [
            "location":   [ 47.763, 13.4034 ],
            "start time": "2018-10-14 10:05:14",
            "HR": 73
            "location":   [ 47.706, 13.2635 ],
            "start time": "2018-10-14 10:39:21",
            "HR": 135
    }

為了檢索可用的軌跡段，你需要使用 .``key 訪問操作符來向下瀏覽周邊的 JSON 對象：

    $.track.segments

要檢索數組的內容，通常使用 [*] 操作符。例如，下面的路徑將返回所有可用軌道段的位置座標：

    $.track.segments[*].location

要只返回第一個段的座標，可以在 [] 訪問操作符中指定相應的下標。重新調用相對於 0 的 JSON 數組索引：

    $.track.segments[0].location

每個路徑求值步驟的結果可以由 SQL/JSON 路徑操作符和方法中列出的一個或多個 jsonpath 操作符和方法來處理。 每個方法名之前必須有一個點。例如，你可以得到一個數組的大小：

    $.track.segments.size()

在路徑運算式中使用 jsonpath 操作符和方法的更多樣本見下面 SQL/JSON 路徑操作符和方法。

在定義路徑時，還可以使用一個或多個與 SQL 中的 WHERE 子句類似的 filter expressions 。 過濾器運算式以問號開頭，並在圓括弧中提供條件：

    ? (condition)

過濾運算式必須在它們應該應用的路徑求值步驟之後寫入。該步驟的結果將被篩選，以只包括滿足所提供條件的那些項。 SQL/JSON 定義了三值邏輯，因此條件可以是 true , false ,或 unknown 。 unknown 值發揮與 SQL NULL 相同的角色，可以使用 is unknown 謂詞進行測試。 進一步的路徑求值步驟只使用篩選器運算式返回 true 的那些項。

可以在過濾運算式中使用的函數和操作符羅列在 jsonpath 過濾器運算式元素表中。 在一個過濾運算式中， @ 變數表示被過濾的值（也就是說，前面路徑步驟的一個結果）。你可以在 @ 後面寫訪問操作符來檢索組件項。

例如，假設你想要檢索所有高於 130 的心率值。你可以使用下面的運算式來實現這一點：

    $.track.segments[*].HR ? (@ > 130)

為了獲得具有這些值的片段的開始時間，必須在返回開始時間之前過濾掉不相關的片段，所以過濾運算式應用於上一步，條件中使用的路徑不同：

    $.track.segments[*] ? (@.HR > 130)."start time"

如果需要，可以按順序使用幾個過濾器運算式。例如，下面的運算式選擇所有包含有相關座標和高心率值的位置的段的開始時間：

    $.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

也允許在不同嵌套層級中使用過濾器運算式。下面的例子首先根據位置篩選所有的片段，然後返回這些片段的高心率值，如果適用的話：

    $.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)

你也可以在彼此之間嵌套過濾器運算式：

    $.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()

如果包含任何具有高心率值的片段，則該運算式返回曲目的大小，否則返回空序列。

本資料庫的 SQL/JSON 路徑語言的實現與 SQL/JSON 標準有以下偏差：

• 路徑運算式可以是布爾謂詞，儘管 SQL/JSON 標準只允許在過濾器中使用謂詞。 這是實現 @@ 操作符所必需的。例如，下面的 jsonpath 運算式在本資料庫中是有效：

        $.track.segments[*].HR < 70

• 在解釋 like_regex 過濾器中使用的Regex模式方面有一些小的差異，如 SQL/JSON Regex中所述。

嚴格的（Strict）和不嚴格的（Lax）模式

當查詢 JSON 資料時，路徑運算式可能與實際的 JSON 資料結構不匹配。 試圖訪問不存在的對象成員或數組元素會導致結構錯誤。SQL/JSON 路徑運算式有兩種處理結構錯誤的模式：

• 不嚴格的（lax）（預設）—路徑引擎隱式地將查詢的資料適配到指定的路徑。任何剩餘的結構錯誤都將被抑制並轉換為空白 SQL/JSON 序列。

• 嚴格的（strict）—如果發生了結構錯誤，則會引發錯誤。

如果 JSON 資料不符合期望的模式，不嚴格的（lax）模式有助於匹配 JSON 文檔結構和路徑運算式。 如果操作不匹配特定操作的要求，可以自動將其封裝為 SQL/JSON 數組，也可以在執行該操作之前將其元素轉換為 SQL/JSON 序列來解封裝。 此外，比較操作符會自動以 lax 模式開啟它們的運算元，因此你可以開包即用的就能比較 SQL/JSON 數組。 大小為 1 的數組被認為等於它的唯一元素。只有在以下情況下才不會自動延伸：

• 路徑運算式包含 type() 或 size() 方法，它們分別返回數組中的元素類型和數量。

• 查詢的 JSON 資料包含嵌套的數組。在本例中，只有最外層的數組被開啟，而所有內部數組保持不變。 因此，隱式展開在每個路徑求值步驟中只能向下進行一級。

例如，當查詢上面列出的 GPS 資料時，當使用不嚴格的（lax）模式時，你可以從它儲存了一組片段的事實中抽象出來：

    lax $.track.segments.location

在嚴格的（strict）模式中，指定的路徑必須與查詢的 JSON 文檔的結構完全符合才能返回 SQL/JSON 項，因此使用該路徑運算式會導致錯誤。 要得到與不嚴格的（lax）模式相同的結果，你必須顯式地開啟 segments 數組：

    strict $.track.segments[*].location

SQL/JSON路徑操作符和方法

jsonpath 操作符和方法表顯示了 jsonpath 中可用的操作符和方法。 請注意，雖然一元操作符和方法可以應用於由前一個路徑步驟產生的多個值，二元操作符（加法等）只能應用於單個值。

jsonpath 操作符和方法

number + number → number 加法 jsonb_path_query('[2]', '$[0] + 3') → 5

+ number → number 一元加號（無操作）；與加法不同，這個可以迭代多個值 jsonb_path_query_array('{"x": [2,3,4]}', '+ $.x') → [2, 3, 4]

number - number → number 減法 jsonb_path_query('[2]', '7 - $[0]') → 5

- number → number 否定;與減法不同，它可以迭代多個值 jsonb_path_query_array('{"x": [2,3,4]}', '- $.x') → [-2, -3, -4]

number * number → number 乘法 jsonb_path_query('[4]', '2 * $[0]') → 8

number / number → number 除法 jsonb_path_query('[8.5]', '$[0] / 2') → 4.2500000000000000

number % number → number 模數（餘數） jsonb_path_query('[32]', '$[0] % 10') → 2

value . type() → string JSON項的類型（參見 json_typeof ） jsonb_path_query_array('[1, "2", {}]', '$[*].type()') → ["number", "string", "object"]

value . size() → number JSON項的大小（數組元素的數量，如果不是數組則為1） jsonb_path_query('{"m": [11, 15]}', '$.m.size()') → 2

value . double() → number 從JSON數字或字串轉換過來的近似浮點數 jsonb_path_query('{"len": "1.9"}', '$.len.double() * 2') → 3.8

number . ceiling() → number 大於或等於給定數位最接近的整數 jsonb_path_query('{"h": 1.3}', '$.h.ceiling()') → 2

number . floor() → number 小於或等於給定數位最近整數 jsonb_path_query('{"h": 1.7}', '$.h.floor()') → 1

number . abs() → number 給定數位絕對值 jsonb_path_query('{"z": -0.3}', '$.z.abs()') → 0.3

string . datetime() → datetime_type (see note) 從字串轉換過來的日期/時間值 jsonb_path_query('["2015-8-1", "2015-08-12"]', '$[*] ? (@.datetime() < "2015-08-2".datetime())') → "2015-8-1"

string . datetime( template ) → datetime_type (see note) 使用指定的 to_timestamp 模板從字串轉換過來的日期/時間值 jsonb_path_query_array('["12:30", "18:40"]', '$[*].datetime("HH24:MI")') → ["12:30:00", "18:40:00"]

object . keyvalue() → array 對象的索引值對，表示為包含三個欄位的對象數組： "key" ， "value" ，和 "id" ; "id" 是索引值對所歸屬對象的唯一識別碼 jsonb_path_query_array('{"x": "20", "y": 32}', '$.keyvalue()') → [{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]

注意

datetime() 和 datetime(``template``) 方法的結果類型可以是 date , timetz , time , timestamptz , 或 timestamp 。 這兩個方法都動態地確定它們的結果類型。

datetime() 方法依次嘗試將其輸入字串與 date , timetz , time , timestamptz , 和 timestamp 的 ISO 格式進行匹配。 它在第一個匹配格式時停止，並發出相應的資料類型。

datetime(``template``) 方法根據所提供的模板字串中使用的欄位確定結果類型。

datetime() 和 datetime(``template``) 方法使用與 to_timestamp SQL 函數相同的解析規則，但有三個例外。 首先，這些方法不允許不匹配的模板模式。 其次，模板字串中只允許以下分隔字元：減號、句點、solidus（斜杠）、逗號、撇號、分號、冒號和空格。 第三，模板字串中的分隔字元必須與輸入字串完全符合。

如果需要比較不同的日期/時間類型，則應用隱式轉換。 date 值可以轉換為 timestamp 或 timestamptz , timestamp 可以轉換為 timestamptz , time 可以轉換為 timetz 。 但是，除了第一個轉換外，其他所有轉換都依賴於當前 TimeZone 設定，因此只能在時區感知的 jsonpath 函數中執行。

jsonpath 過濾器運算式元素表顯示了適用的過濾器運算式元素。

jsonpath 過濾器運算式元素

value == value → boolean 相等比較（這個，和其他比較操作符，適用於所有JSON標量值） jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == 1)') → [1, 1] jsonb_path_query_array('[1, "a", 1, 3]', '$[*] ? (@ == "a")') → ["a"]

value != value → boolean value <> value → boolean 不相等比較 jsonb_path_query_array('[1, 2, 1, 3]', '$[*] ? (@ != 1)') → [2, 3] jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <> "b")') → ["a", "c"]

value < value → boolean 小於比較 jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ < 2)') → [1]

value <= value → boolean 小於或等於比較 jsonb_path_query_array('["a", "b", "c"]', '$[*] ? (@ <= "b")') → ["a", "b"]

value > value → boolean 大於比較 jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ > 2)') → [3]

value >= value → boolean 大於或等於比較 jsonb_path_query_array('[1, 2, 3]', '$[*] ? (@ >= 2)') → [2, 3]

true → boolean JSON常數 真 jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == true)') → {"name": "Chris", "parent": true}

false → boolean JSON常數 假 jsonb_path_query('[{"name": "John", "parent": false}, {"name": "Chris", "parent": true}]', '$[*] ? (@.parent == false)') → {"name": "John", "parent": false}

null → value JSON常數 null （注意，與SQL不同，與 null 比較可以正常工作） jsonb_path_query('[{"name": "Mary", "job": null}, {"name": "Michael", "job": "driver"}]', '$[*] ? (@.job == null) .name') → "Mary"

boolean && boolean → boolean 布爾 AND jsonb_path_query('[1, 3, 7]', '$[*] ? (@ > 1 && @ < 5)') → 3

boolean || boolean → boolean 布爾 OR jsonb_path_query('[1, 3, 7]', '$[*] ? (@ < 1 || @ > 5)') → 7

! boolean → boolean 布爾 NOT jsonb_path_query('[1, 3, 7]', '$[*] ? (!(@ < 5))') → 7

boolean is unknown → boolean 測試布爾條件是否為 unknown 。 jsonb_path_query('[-1, 2, 7, "foo"]', '$[*] ? ((@ > 0) is unknown)') → "foo"

string like_regex string [ flag string ] → boolean 測試第一個運算元是否與第二個運算元給出的Regex匹配，可選使用由一串 flag 字元描述的修改。 jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c")') → ["abc", "abdacb"] jsonb_path_query_array('["abc", "abd", "aBdC", "abdacb", "babc"]', '$[*] ? (@ like_regex "^ab.*c" flag "i")') → ["abc", "aBdC", "abdacb"]

string starts with string → boolean 測試第二個運算元是否為第一個運算元的初始子串。 jsonb_path_query('["John Smith", "Mary Stone", "Bob Johnson"]', '$[*] ? (@ starts with "John")') → "John Smith"

exists ( path_expression ) → boolean 測試路徑運算式是否至少匹配一個SQL/JSON項。 如果路徑運算式會導致錯誤，則返回 unknown ;第二個例子使用這個方法來避免在strict 模式下出現無此鍵（no-such-key）錯誤。 jsonb_path_query('{"x": [1, 2], "y": [2, 4]}', 'strict $.* ? (exists (@ ? (@[*] > 2)))') → [2, 4] jsonb_path_query_array('{"value": 41}', 'strict $ ? (exists (@.name)) .name') → []

SQL/JSONRegex

SQL/JSON路徑運算式允許通過 like_regex 過濾器將文本匹配為Regex。 例如，下面的 SQL/JSON 路徑查詢將不區分大小寫地匹配以英語母音開頭的數組中的所有字串：

    $[*] ? (@ like_regex "^[aeiou]" flag "i")

可選的 flag 字串可以包括一個或多個字元 i 用於不區分大小寫匹配， m 允許 ^ 和 $ 在換行時匹配， s 允許 . 匹配分行符號， q 引用整個模式（將行為簡化為一個簡單的子字串匹配）。

SQL/JSON 標準借用了來自 LIKE_REGEX 操作符的Regex定義，其使用了 XQuery 標準。 PostgreSQL 目前不支援 LIKE_REGEX 操作符。因此， like_regex 過濾器是使用 POSIX Regex中描述的 POSIX Regex引擎來實現的，這導致了與標準 SQL/JSON 行為的各種細微差異。 但是請注意，這裡描述的標誌字母不相容並不適用於 SQL/JSON，因為它將 XQuery 標誌字母翻譯為符合 POSIX 引擎的預期。

請記住， like_regex 的模式參數是一個 JSON 路徑字串文字，根據 jsonpath Type 給出的規則編寫。 這特別意味著在Regex中要使用的任何反斜線都必須加倍。例如，匹配只包含數位字串：

    $ ? (@ like_regex "^\\d+$")