From a570c98d7fa0841f17bbf51d62d02d9e493c7fcc Mon Sep 17 00:00:00 2001
From: Andrew Dunstan
Date: Fri, 29 Mar 2013 14:12:13 -0400
Subject: Add new JSON processing functions and parser API.
The JSON parser is converted into a recursive descent parser, and
exposed for use by other modules such as extensions. The API provides
hooks for all the significant parser event such as the beginning and end
of objects and arrays, and providing functions to handle these hooks
allows for fairly simple construction of a wide variety of JSON
processing functions. A set of new basic processing functions and
operators is also added, which use this API, including operations to
extract array elements, object fields, get the length of arrays and the
set of keys of a field, deconstruct an object into a set of key/value
pairs, and create records from JSON objects and arrays of objects.
Catalog version bumped.
Andrew Dunstan, with some documentation assistance from Merlin Moncure.
---
doc/src/sgml/func.sgml | 242 ++++++++++++++++++++++++++++++++++++++++++++++++-
1 file changed, 239 insertions(+), 3 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a0ecc4f568..b5d9160040 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -9846,17 +9846,75 @@ table2-mapping
Functions and operators
+
+ shows the operators that are
+ available for use with JSON (see ) data.
+
+
+
+ JSON Operators
+
+
+
+ Operator
+ Right Operand Type
+ Description
+ Example
+
+
+
+
+ ->
+ int
+ Get JSON array element
+ '[1,2,3]'::json->2
+
+
+ ->
+ text
+ Get JSON object field
+ '{"a":1,"b":2}'::json->'b'
+
+
+ ->>
+ int
+ Get JSON array element as text
+ '[1,2,3]'::json->>2
+
+
+ ->>
+ text
+ Get JSON object field as text
+ '{"a":1,"b":2}'::json->>'b'
+
+
+ #>
+ array of text
+ Get JSON object at specified path
+ '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
+
+
+ #>>
+ array of text
+ Get JSON object at specified path as text
+ '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'
+
+
+
+
+
shows the functions that are available
- for creating JSON (see ) data.
+ for creating and manipulating JSON (see ) data.
JSON Support Functions
-
+
Function
+ Return Type
Description
Example
Example Result
@@ -9870,6 +9928,7 @@ table2-mapping
array_to_json(anyarray [, pretty_bool])
+ json
Returns the array as JSON. A PostgreSQL multidimensional array
becomes a JSON array of arrays. Line feeds will be added between
@@ -9885,6 +9944,7 @@ table2-mapping
row_to_json(record [, pretty_bool])
+ json
Returns the row as JSON. Line feeds will be added between level
1 elements if pretty_bool is true.
@@ -9899,6 +9959,7 @@ table2-mapping
to_json(anyelement)
+ json
Returns the value as JSON. If the data type is not builtin, and there
is a cast from the type to json, the cast function will be used to
@@ -9909,6 +9970,182 @@ table2-mapping
to_json('Fred said "Hi."'
"Fred said \"Hi.\""
+
+
+
+ json_array_length
+
+ json_array_length(json)
+
+ int
+
+ Returns the number of elements in the outermost json array.
+
+ json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
+ 5
+
+
+
+
+ json_each
+
+ json_each(json)
+
+ SETOF key text, value json
+
+ Expands the outermost json object into a set of key/value pairs.
+
+ select * from json_each_as_text('{"a":"foo", "b":"bar"}')
+
+
+ key | value
+-----+-------
+ a | "foo"
+ b | "bar"
+
+
+
+
+
+
+ json_each_text
+
+ json_each_text(from_json json)
+
+ SETOF key text, value text
+
+ Expands the outermost json object into a set of key/value pairs. The
+ returned value will be of type text.
+
+ select * from json_each_as_text('{"a":"foo", "b":"bar"}')
+
+
+ key | value
+-----+-------
+ a | foo
+ b | bar
+
+
+
+
+
+
+ json_extract_path
+
+ json_extract_path(from_json json, VARIADIC path_elems text[])
+
+ json
+
+ Returns json object pointed to by path_elems.
+
+ json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
+ {"f5":99,"f6":"foo"}
+
+
+
+
+ json_extract_path_text
+
+ json_extract_path_text(from_json json, VARIADIC path_elems text[])
+
+ text
+
+ Returns json object pointed to by path_elems.
+
+ json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')
+ foo
+
+
+
+
+ json_object_keys
+
+ json_object_keys(json)
+
+ SETOF text
+
+ Returns set of keys in the json object. Only the "outer" object will be displayed.
+
+ json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')
+
+
+ json_object_keys
+------------------
+ f1
+ f2
+
+
+
+
+
+
+ json_populate_record
+
+ json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]
+
+ anyelement
+
+ Expands the object in from_json to a row whose columns match
+ the record type defined by base. Conversion will be best
+ effort; columns in base with no corresponding key in from_json
+ will be left null. A column may only be specified once.
+
+ json_populate_record(null::x, '{"a":1,"b":2}')
+
+
+ a | b
+---+---
+ 1 | 2
+
+
+
+
+
+
+ json_populate_recordset
+
+ json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]
+
+ SETOF anyelement
+
+ Expands the outermost set of objects in from_json to a set
+ whose columns match the record type defined by base.
+ Conversion will be best effort; columns in base with no
+ corresponding key in from_json will be left null. A column
+ may only be specified once.
+
+ json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')
+
+
+ a | b
+---+---
+ 1 | 2
+ 3 | 4
+
+
+
+
+
+
+ json_array_elements
+
+ json_array_elements(json)
+
+ SETOF json
+
+ Expands a json array to a set of json elements.
+
+ json_array_elements('[1,true, [2,false]]')
+
+
+ value
+-----------
+ 1
+ true
+ [2,false]
+
+
+
@@ -9926,7 +10163,6 @@ table2-mapping
function json_agg which aggregates record
values as json efficiently.
-
--
cgit v1.2.3