json_array_length(json)
jsonb_array_length(jsonb)
int Returns the number of elements in the outermost JSON array. json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 5json_each(json)
jsonb_each(jsonb)
setof key text, value json
setof key text, value jsonb
Expands the outermost JSON object into a set of key/value pairs. select * from json_each('{"a":"foo", "b":"bar"}')key | value -----+------- a | "foo" b | "bar"
json_each_text(json)
jsonb_each_text(jsonb)
setof key text, value text Expands the outermost JSON object into a set of key/value pairs. The returned values will be of type text. 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[])
jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])
json
jsonb
Returns JSON value pointed to by path_elems (equivalent to #> operator). json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') {"f5":99,"f6":"foo"}json_extract_path_text(from_json json, VARIADIC path_elems text[])
jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])
text Returns JSON value pointed to by path_elems as text (equivalent to #>> operator). json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') foojson_object_keys(json)
jsonb_object_keys(jsonb)
setof text Returns set of keys in the outermost JSON object. json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')json_object_keys ------------------ f1 f2
json_populate_record(base anyelement, from_json json)
jsonb_populate_record(base anyelement, from_json jsonb)
anyelement Expands the object in from_json to a row whose columns match the record type defined by base (see note below). select * from json_populate_record(null::myrowtype, '{"a":1,"b":2}')a | b ---+--- 1 | 2
json_populate_recordset(base anyelement, from_json json)
jsonb_populate_recordset(base anyelement, from_json jsonb)
setof anyelement Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base (see note below). select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')a | b ---+--- 1 | 2 3 | 4
json_array_elements(json)
jsonb_array_elements(jsonb)
setof json
setof jsonb
Expands a JSON array to a set of JSON values. select * from json_array_elements('[1,true, [2,false]]')value ----------- 1 true [2,false]
json_array_elements_text(json)
jsonb_array_elements_text(jsonb)
setof text Expands a JSON array to a set of text values. select * from json_array_elements_text('["foo", "bar"]')value ----------- foo bar
json_typeof(json)
jsonb_typeof(jsonb)
text Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null. json_typeof('-123.4') numberjson_to_record(json)
jsonb_to_record(jsonb)
record Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause. select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text)a | b | d ---+---------+--- 1 | [1,2,3] |
json_to_recordset(json)
jsonb_to_recordset(jsonb)
setof record Builds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause. select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);a | b ---+----- 1 | foo 2 |
json_strip_nulls(from_json json)
jsonb_strip_nulls(from_json jsonb)
json
jsonb
Returns from_json with all object fields that have null values omitted. Other null values are untouched. json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') [{"f1":1},2,null,3]jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])
jsonb
Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays.jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)
jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')
[{"f1":[2,3,4],"f2":null},2,null,3]
[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
jsonb_pretty(from_json jsonb)
text
Returns from_json as indented JSON text. jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')[
{
"f1": 1,
"f2": null
},
2,
null,
3
]
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4