PostgreSQL JSON

朴灿烈づ我的快乐病毒、 2022-07-12 02:16 86阅读 0赞

[PostgreSQL 9.3.4 Documentation][]

<table style="margin-left:0px"> 
 <tbody> 
  <tr> 
   <th colspan="5" align="center">&nbsp;</th> 
  </tr> 
  <tr> 
   <td align="left"><a title="XML Functions" href="http://www.postgresql.org/docs/9.3/static/functions-xml.html" style="color:rgb(0,78,102)" rel="nofollow">Prev</a></td> 
   <td align="left"><a href="http://www.postgresql.org/docs/9.3/static/functions.html" style="color:rgb(0,78,102)" rel="nofollow">Up</a></td> 
   <td align="center">Chapter 9. Functions and Operators</td> 
   <td align="right"><a title="Sequence Manipulation Functions" href="http://www.postgresql.org/docs/9.3/static/functions-sequence.html" style="color:rgb(0,78,102)" rel="nofollow">Next</a></td> 
  </tr> 
 </tbody> 
</table>

--------------------

# 9.15. JSON Functions and Operators #

[Table 9-39][] shows the operators that are available for use with JSON (see [Section 8.14][]) data.

Table 9-39. JSON Operators

<table style="">       
 <thead> 
  <tr> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Operator</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Right Operand Type</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Description</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Example</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> -&gt;</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> int</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Get JSON array element</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> '[1,2,3]'::json-&gt;2</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> -&gt;</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Get JSON object field</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> '{"a":1,"b":2}'::json-&gt;'b'</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> -&gt;&gt;</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> int</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Get JSON array element as text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> '[1,2,3]'::json-&gt;&gt;2</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> -&gt;&gt;</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Get JSON object field as text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> '{"a":1,"b":2}'::json-&gt;&gt;'b'</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> #&gt;</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> array of text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Get JSON object at specified path</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> '{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;'{a,2}'</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> #&gt;&gt;</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> array of text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Get JSON object at specified path as text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> '{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;&gt;'{a,2}'</td> 
  </tr> 
 </tbody> 
</table>

[Table 9-40][] shows the functions that are available for creating and manipulating JSON (see [Section 8.14][]) data.

Table 9-40. JSON Support Functions

<table style="">        
 <thead> 
  <tr> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Function</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Return Type</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Description</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Example</th> 
   <th style="border:1px solid rgb(167,198,223); padding:0.5ex">Example Result</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> array_to_json(anyarray [, pretty_bool])</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Returns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays. Line feeds will be added between dimension 1 elements if&nbsp;pretty_bool&nbsp;is true.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> array_to_json('{ {1,5},{99,100}}'::int[])</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> [[1,5],[99,100]]</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> row_to_json(record [, pretty_bool])</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Returns the row as JSON. Line feeds will be added between level 1 elements if&nbsp;pretty_bool&nbsp;is true.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> row_to_json(row(1,'foo'))</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> {"f1":1,"f2":"foo"}</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> to_json(anyelement)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Returns the value as JSON. If the data type is not built in, and there is a cast from the type to&nbsp;json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value, the text representation will be used, escaped and quoted so that it is legal JSON.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> to_json('Fred said "Hi."'::text)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> "Fred said \"Hi.\""</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_array_length(json)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> int</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Returns the number of elements in the outermost JSON array.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> 5</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_each(json)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> SETOF key text, value json</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Expands the outermost JSON object into a set of key/value pairs.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> select * from json_each('{"a":"foo", "b":"bar"}')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> <pre style=""> key | value
-----+-------
 a   | "foo"
 b   | "bar"
 
</pre></td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_each_text(from_json json)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> SETOF key text, value text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Expands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> select * from json_each_text('{"a":"foo", "b":"bar"}')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> <pre style=""> key | value
-----+-------
 a   | foo
 b   | bar
 
</pre></td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_extract_path(from_json json, VARIADIC path_elems text[])</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Returns JSON object pointed to by&nbsp;path_elems.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> {"f5":99,"f6":"foo"}</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(239,239,239); padding:0.5ex"> json_extract_path_text(from_json json, VARIADIC path_elems text[])</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(239,239,239); padding:0.5ex"> text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(239,239,239); padding:0.5ex"> Returns JSON object pointed to by&nbsp;path_elems.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(239,239,239); padding:0.5ex"> json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(239,239,239); padding:0.5ex"> foo</td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_object_keys(json)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> SETOF text</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Returns set of keys in the JSON object. Only the&nbsp;<span>"outer"</span>&nbsp;object will be displayed.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> <pre style=""> json_object_keys
------------------
 f1
 f2
</pre></td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> anyelement</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Expands the object in&nbsp;from_json&nbsp;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&nbsp;from_json&nbsp;will be left null. If a column is specified more than once, the last value is used.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> select * from json_populate_record(null::x, '{"a":1,"b":2}')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> <pre style=""> a | b
---+---
 1 | 2
</pre></td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> SETOF anyelement</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Expands the outermost set of objects in&nbsp;from_json&nbsp;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&nbsp;from_json&nbsp;will be left null. If a column is specified more than once, the last value is used.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> <pre style=""> a | b
---+---
 1 | 2
 3 | 4
 
</pre></td> 
  </tr> 
  <tr> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_array_elements(json)</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> SETOF json</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> Expands a JSON array to a set of JSON elements.</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> json_array_elements('[1,true, [2,false]]')</td> 
   <td style="border:1px solid rgb(167,198,223); background-color:rgb(255,255,255); padding:0.5ex"> <pre style="">   value
-----------
 1
 true
 [2,false]
</pre></td> 
  </tr> 
 </tbody> 
</table>

> **Note:** The json functions and operators can impose stricter validity requirements than the type's input functions. In particular, they check much more closely that any use of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual Plane is correct.

> **Note:** Many of these functions and operators will convert Unicode escapes in the JSON text to the appropriate UTF8 character when the database encoding is UTF8. In other encodings the escape sequence must be for an ASCII character, and any other code point in a Unicode escape sequence will result in an error. In general, it is best to avoid mixing Unicode escapes in JSON with a non-UTF8 database encoding, if possible.

> **Note:** The [hstore][] extension has a cast from hstore to json, so that converted hstore values are represented as JSON objects, not as string values.

See also [Section 9.20][] about the aggregate function `json_agg` which aggregates record values as JSON efficiently.

[PostgreSQL 9.3.4 Documentation]: http://www.postgresql.org/docs/9.3/static/index.html
[Table 9-39]: http://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-OP-TABLE
[Section 8.14]: http://www.postgresql.org/docs/9.3/static/datatype-json.html
[Table 9-40]: http://www.postgresql.org/docs/9.3/static/functions-json.html#FUNCTIONS-JSON-TABLE
[hstore]: http://www.postgresql.org/docs/9.3/static/hstore.html
[Section 9.20]: http://www.postgresql.org/docs/9.3/static/functions-aggregate.html