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