PostgreSQL JSON 函數(shù)和操作符

json -> integer → json

jsonb -> integer → jsonb

提取JSON數(shù)組的第n個元素(數(shù)組元素從0開始索引，但負(fù)整數(shù)從末尾開始計數(shù))。

'[{"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數(shù)組的第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子對象，路徑元素可以是字段鍵或數(shù)組索引。

'{"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 → boolean

第一個JSON值是否包含第二個?(請參見第 8.14.3 節(jié)以了解包含的詳細(xì)信息。)

'{"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值中的頂級鍵或數(shù)組元素存在?

'{"a":1, "b":2}'::jsonb ? 'b' → t

'["a", "b", "c"]'::jsonb ? 'b' → t

jsonb ?| text[] → boolean

文本數(shù)組中的字符串是否作為頂級鍵或數(shù)組元素存在?

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

jsonb ?& text[] → boolean

文本數(shù)組中的所有字符串都作為頂級鍵或數(shù)組元素存在嗎?

'["a", "b", "c"]'::jsonb ?& array['a', 'b'] → t

jsonb || jsonb → jsonb

連接兩個jsonb值。連接兩個數(shù)組將生成一個包含每個輸入的所有元素的數(shù)組。連接兩個對象將生成一個包含它們鍵的并集的對象，當(dāng)存在重復(fù)的鍵時取第二個對象的值。 所有其他情況都是通過將非數(shù)組輸入轉(zhuǎn)換為單個元素數(shù)組，然后按照兩個數(shù)組的方式進(jìn)行處理。 不遞歸操作:只有頂級數(shù)組或?qū)ο蠼Y(jié)構(gòu)被合并。

'["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數(shù)組中刪除匹配的字符串值。

'{"a": "b", "c": "d"}'::jsonb - 'a' → {"c": "d"}

'["a", "b", "c", "b"]'::jsonb - 'b' → ["a", "c"]

jsonb - text[] → jsonb

從左操作數(shù)中刪除所有匹配的鍵或數(shù)組元素。

'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[] → {}

jsonb - integer → jsonb

刪除具有指定索引的數(shù)組元素(負(fù)整數(shù)從末尾計數(shù))。如果JSON值不是數(shù)組，則拋出錯誤。

'["a", "b"]'::jsonb - 1 → ["a"]

jsonb #- text[] → jsonb

刪除指定路徑上的字段或數(shù)組元素，路徑元素可以是字段鍵或數(shù)組索引。

'["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路徑謂詞檢查的結(jié)果。只考慮結(jié)果的第一項。如果結(jié)果不是布爾值，則返回NULL。

'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t

to_json ( anyelement ) → json

to_jsonb ( anyelement ) → jsonb

將任何SQL值轉(zhuǎn)換為json或jsonb。數(shù)組和組合遞歸地轉(zhuǎn)換為數(shù)組和對象(多維數(shù)組在JSON中變成數(shù)組的數(shù)組)。 否則，如果存在從SQL數(shù)據(jù)類型到json的類型轉(zhuǎn)換，則造型函數(shù)將用于執(zhí)行轉(zhuǎn)換; ^[a] 否則，將生成一個標(biāo)量json值。對于除數(shù)字、布爾值或空值之外的任何標(biāo)量，將使用文本表示，并根據(jù)需要進(jìn)行轉(zhuǎn)義，使其成為有效的JSON字符串值。

to_json('Fred said "Hi."'::text) → "Fred said \"Hi.\""

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

array_to_json ( anyarray [, boolean ] ) → json

將SQL數(shù)組轉(zhuǎn)換為JSON數(shù)組。該行為與to_json相同，只是如果可選boolean參數(shù)為真，換行符將在頂級數(shù)組元素之間添加。

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

row_to_json ( record [, boolean ] ) → json

將SQL組合值轉(zhuǎn)換為JSON對象。該行為與to_json相同，只是如果可選boolean參數(shù)為真，換行符將在頂級元素之間添加。

row_to_json(row(1,'foo')) → {"f1":1,"f2":"foo"}

json_build_array ( VARIADIC "any" ) → json

jsonb_build_array ( VARIADIC "any" ) → jsonb

根據(jù)可變參數(shù)列表構(gòu)建可能異構(gòu)類型的JSON數(shù)組。每個參數(shù)都按照to_json或to_jsonb進(jìn)行轉(zhuǎn)換。

json_build_array(1, 2, 'foo', 4, 5) → [1, 2, "foo", 4, 5]

json_build_object ( VARIADIC "any" ) → json

jsonb_build_object ( VARIADIC "any" ) → jsonb

根據(jù)可變參數(shù)列表構(gòu)建一個JSON對象。按照慣例，參數(shù)列表由交替的鍵和值組成。 關(guān)鍵參數(shù)強(qiáng)制轉(zhuǎn)換為文本;值參數(shù)按照to_json或to_jsonb進(jìn)行轉(zhuǎn)換。

json_build_object('foo', 1, 2, row(3,'bar')) → {"foo" : 1, "2" : {"f1":3,"f2":"bar"}}

json_object ( text[] ) → json

jsonb_object ( text[] ) → jsonb

從文本數(shù)組構(gòu)建JSON對象。該數(shù)組必須有兩個維度，一個維度的成員數(shù)為偶數(shù)，在這種情況下，它們被視為交替的鍵/值對; 另一個維度的成員數(shù)為二維，每個內(nèi)部數(shù)組恰好有兩個元素，它們被視為鍵/值對。所有值都轉(zhuǎn)換為JSON字符串。

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"}

json_object ( keys text[], values text[] ) → json

jsonb_object ( keys text[], values text[] ) → jsonb

這種形式的json_object從單獨(dú)的文本數(shù)組中成對地獲取鍵和值。否則，它與單參數(shù)形式相同。

json_object('{a,b}', '{1,2}') → {"a": "1", "b": "2"}

json_array_elements ( json ) → setof json

jsonb_array_elements ( jsonb ) → setof jsonb

將頂級JSON數(shù)組展開為一組JSON值。

select * from json_array_elements('[1,true, [2,false]]') →

   value
-----------
 1
 true
 [2,false]

json_array_elements_text ( json ) → setof text

jsonb_array_elements_text ( jsonb ) → setof text

將頂級JSON數(shù)組展開為一組文本值。

select * from json_array_elements_text('["foo", "bar"]') →

   value
-----------
 foo
 bar

json_array_length ( json ) → integer

jsonb_array_length ( jsonb ) → integer

返回頂級JSON數(shù)組中的元素數(shù)量。

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

json_each ( json ) → setof record ( key text, value json )

jsonb_each ( jsonb ) → setof record ( key text, value jsonb)

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

select * from json_each('{"a":"foo", "b":"bar"}') →

 key | value
-----+-------
 a   | "foo"
 b   | "bar"

json_each_text ( json ) → setof record ( key text, value text)

jsonb_each_text ( jsonb ) → setof record ( key text, value text)

將頂級JSON對象擴(kuò)展為一組鍵/值對。返回的值的類型為文本。

select * from json_each_text('{"a":"foo", "b":"bar"}') →

 key | value
-----+-------
 a   | foo
 b   | bar

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

在指定路徑下提取JSON子對象。(這在功能上相當(dāng)于#>操作符，但在某些情況下，將路徑寫成可變參數(shù)列表會更方便。)

json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → "foo"

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子對象提取為文本。(這在功能上等同于#>>操作符。)

json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6') → foo

json_object_keys ( json ) → setof text

jsonb_object_keys ( jsonb ) → setof text

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

select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') →

 json_object_keys
------------------
 f1
 f2

json_populate_record ( base anyelement, from_json json ) → anyelement

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

將頂級JSON對象擴(kuò)展為具有基本參數(shù)的復(fù)合類型的行。JSON對象將被掃描，查找名稱與輸出行類型的列名匹配的字段，并將它們的值插入到輸出的這些列中。 (不對應(yīng)任何輸出列名的字段將被忽略。)在典型的使用中，基本的值僅為NULL，這意味著任何不匹配任何對象字段的輸出列都將被填充為空。 但是，如果 base 不為NULL，那么它包含的值將用于不匹配的列。

要將JSON值轉(zhuǎn)換為輸出列的SQL類型，需要按次序應(yīng)用以下規(guī)則:

雖然下面的示例使用一個常量JSON值，典型的用法是在查詢的FROM子句中從另一個表側(cè)面地引用json或jsonb列。 在FROM子句中編寫json_populate_record是一種很好的實踐，因為提取的所有列都可以使用，而不需要重復(fù)的函數(shù)調(diào)用。

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")

json_populate_recordset ( base anyelement, from_json json ) → setof anyelement

jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof anyelement

將對象的頂級JSON數(shù)組展開為一組具有基本參數(shù)的復(fù)合類型的行。 對于json[b]_populate_record，將如上所述處理JSON數(shù)組的每個元素。

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

json_to_record ( json ) → record

jsonb_to_record ( jsonb ) → record

將頂級JSON對象展開為具有由 AS子句定義的復(fù)合類型的行。 (與所有返回record的函數(shù)一樣，調(diào)用查詢必須使用AS子句顯式定義記錄的結(jié)構(gòu)。) 輸出記錄由JSON對象的字段填充，與上面描述的json[b]_populate_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")

json_to_recordset ( json ) → setof record

jsonb_to_recordset ( jsonb ) → setof record

將頂級JSON對象數(shù)組展開為一組由AS子句定義的復(fù)合類型的行。 (與所有返回record的函數(shù)一樣，調(diào)用查詢必須使用AS子句顯式定義記錄的結(jié)構(gòu)。) 對于json[b]_populate_record，將如上所述處理JSON數(shù)組的每個元素。

select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text) →

 a |  b
---+-----
 1 | foo
 2 |

jsonb_set ( target jsonb, path text[], new_value jsonb[, create_if_missing boolean ] ) → jsonb

返回target，將path指定的項替換為new_value， 如果create_if_missing為真(此為默認(rèn)值)并且path指定的項不存在，則添加 new_value 。 路徑中的所有前面步驟都必須存在，否則將不加改變地返回target。 與面向路徑操作符一樣，負(fù)整數(shù)出現(xiàn)在JSON數(shù)組末尾的path計數(shù)中。 如果最后一個路徑步驟是超出范圍的數(shù)組索引，并且create_if_missing為真，那么如果索引為負(fù)，新值將添加到數(shù)組的開頭，如果索引為正，則添加到數(shù)組的結(jié)尾。

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]

jsonb_set_lax ( target jsonb, path text[], new_value jsonb[, create_if_missing boolean [, null_value_treatment text ]] ) → jsonb

如果new_value不為NULL，則行為與jsonb_set完全一樣。 否則，根據(jù)null_value_treatment的值，它必須是'raise_exception'， 'use_json_null', 'delete_key', 或'return_target'。 默認(rèn)值為'use_json_null'。

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]

jsonb_insert ( target jsonb, path text[], new_value jsonb[, insert_after boolean ] ) → jsonb

返回插入new_value的target。 如果path指派的項是一個數(shù)組元素，如果 insert_after為假(此為默認(rèn)值)，則new_value將被插入到該項之前，如果 insert_after 為真則在該項之后。 如果由path指派的項是一個對象字段，則只在對象不包含該鍵時才插入 new_value。 路徑中的所有前面步驟都必須存在，否則將不加改變地返回target。 與面向路徑操作符一樣，負(fù)整數(shù)出現(xiàn)在JSON數(shù)組末尾的 path計數(shù)中。 如果最后一個路徑步驟是超出范圍的數(shù)組下標(biāo)，則如果下標(biāo)為負(fù)，則將新值添加到數(shù)組的開頭;如果下標(biāo)為正，則將新值添加到數(shù)組的結(jié)尾。

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]}

json_strip_nulls ( json ) → json

jsonb_strip_nulls ( jsonb ) → jsonb

從給定的JSON值中刪除所有具有空值的對象字段，遞歸地。非對象字段的空值是未受影響的。

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

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

檢查JSON路徑是否返回指定JSON值的任何項。如果指定了vars參數(shù)，則它必須是一個JSON對象，并且它的字段提供要替換到jsonpath表達(dá)式中的名稱值。 如果指定了silent參數(shù)并為true，函數(shù)會抑制與@? 和 @@運(yùn)算符相同的錯誤。

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

jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → boolean

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

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

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

為指定的JSON值返回由JSON路徑返回的所有JSON項?？蛇x的vars和silent參數(shù)的作用與jsonb_path_exists相同。

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}') →

 jsonb_path_query
------------------
 2
 3
 4

jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

以JSON數(shù)組的形式返回由JSON路徑為指定的JSON值返回的所有JSON項?？蛇x的vars和silent參數(shù)的作用與jsonb_path_exists相同。

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

jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → jsonb

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

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

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

這些函數(shù)與上面描述的沒有_tz后綴的對應(yīng)函數(shù)類似，除了這些函數(shù)支持需要時區(qū)感知轉(zhuǎn)換的日期/時間值比較之外。 下面的示例需要將只包含日期的值2015-08-02解釋為帶有時區(qū)的時間戳，因此結(jié)果依賴于當(dāng)前TimeZone設(shè)置。 由于這種依賴性，這些函數(shù)被標(biāo)記為穩(wěn)定的，這意味著不能在索引中使用這些函數(shù)。 它們的對應(yīng)項是不可改變的，因此可以用于索引;但是，如果要求他們進(jìn)行這樣的比較，他們就會拋出錯誤。

jsonb_path_exists_tz('["2015-08-01 12:00:00 -05"]', '$[*] ? (@.datetime() < "2015-08-02".datetime())') → t

jsonb_pretty ( jsonb ) → text

將給定的JSON值轉(zhuǎn)換為精美打印的，縮進(jìn)的文本。

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

[
    {
        "f1": 1,
        "f2": null
    },
    2
]

json_typeof ( json ) → text

jsonb_typeof ( jsonb ) → text

以文本字符串形式返回頂級JSON值的類型?？赡艿念愋陀?code class="literal">object, array,string, number,boolean, 和 null。 ( null的結(jié)果不應(yīng)該與SQL NULL 混淆;參見示例。)

json_typeof('-123.4') → number

json_typeof('null'::json) → null

json_typeof(NULL::json) IS NULL → t