Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita570c98

Browse files
committed
Add new JSON processing functions and parser API.
The JSON parser is converted into a recursive descent parser, andexposed for use by other modules such as extensions. The API provideshooks for all the significant parser event such as the beginning and endof objects and arrays, and providing functions to handle these hooksallows for fairly simple construction of a wide variety of JSONprocessing functions. A set of new basic processing functions andoperators is also added, which use this API, including operations toextract array elements, object fields, get the length of arrays and theset of keys of a field, deconstruct an object into a set of key/valuepairs, and create records from JSON objects and arrays of objects.Catalog version bumped.Andrew Dunstan, with some documentation assistance from Merlin Moncure.
1 parent9ad27c2 commita570c98

File tree

12 files changed

+3579
-310
lines changed

12 files changed

+3579
-310
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 239 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9846,17 +9846,75 @@ table2-mapping
98469846
<secondary>Functions and operators</secondary>
98479847
</indexterm>
98489848

9849+
<para>
9850+
<xref linkend="functions-json-op-table"> shows the operators that are
9851+
available for use with JSON (see <xref linkend="datatype-json">) data.
9852+
</para>
9853+
9854+
<table id="functions-json-op-table">
9855+
<title>JSON Operators</title>
9856+
<tgroup cols="4">
9857+
<thead>
9858+
<row>
9859+
<entry>Operator</entry>
9860+
<entry>Right Operand Type</entry>
9861+
<entry>Description</entry>
9862+
<entry>Example</entry>
9863+
</row>
9864+
</thead>
9865+
<tbody>
9866+
<row>
9867+
<entry><literal>-&gt;</literal></entry>
9868+
<entry>int</entry>
9869+
<entry>Get JSON array element</entry>
9870+
<entry><literal>'[1,2,3]'::json-&gt;2</literal></entry>
9871+
</row>
9872+
<row>
9873+
<entry><literal>-&gt;</literal></entry>
9874+
<entry>text</entry>
9875+
<entry>Get JSON object field</entry>
9876+
<entry><literal>'{"a":1,"b":2}'::json-&gt;'b'</literal></entry>
9877+
</row>
9878+
<row>
9879+
<entry><literal>-&gt;&gt;</literal></entry>
9880+
<entry>int</entry>
9881+
<entry>Get JSON array element as text</entry>
9882+
<entry><literal>'[1,2,3]'::json-&gt;&gt;2</literal></entry>
9883+
</row>
9884+
<row>
9885+
<entry><literal>-&gt;&gt;</literal></entry>
9886+
<entry>text</entry>
9887+
<entry>Get JSON object field as text</entry>
9888+
<entry><literal>'{"a":1,"b":2}'::json-&gt;&gt;'b'</literal></entry>
9889+
</row>
9890+
<row>
9891+
<entry><literal>#&gt;</literal></entry>
9892+
<entry>array of text</entry>
9893+
<entry>Get JSON object at specified path</entry>
9894+
<entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;'{a,2}'</literal></entry>
9895+
</row>
9896+
<row>
9897+
<entry><literal>#&gt;&gt;</literal></entry>
9898+
<entry>array of text</entry>
9899+
<entry>Get JSON object at specified path as text</entry>
9900+
<entry><literal>'{"a":[1,2,3],"b":[4,5,6]}'::json#&gt;&gt;'{a,2}'</literal></entry>
9901+
</row>
9902+
</tbody>
9903+
</tgroup>
9904+
</table>
9905+
98499906
<para>
98509907
<xref linkend="functions-json-table"> shows the functions that are available
9851-
for creating JSON (see <xref linkend="datatype-json">) data.
9908+
for creatingand manipulatingJSON (see <xref linkend="datatype-json">) data.
98529909
</para>
98539910

98549911
<table id="functions-json-table">
98559912
<title>JSON Support Functions</title>
9856-
<tgroup cols="4">
9913+
<tgroup cols="5">
98579914
<thead>
98589915
<row>
98599916
<entry>Function</entry>
9917+
<entry>Return Type</entry>
98609918
<entry>Description</entry>
98619919
<entry>Example</entry>
98629920
<entry>Example Result</entry>
@@ -9870,6 +9928,7 @@ table2-mapping
98709928
</indexterm>
98719929
<literal>array_to_json(anyarray [, pretty_bool])</literal>
98729930
</entry>
9931+
<entry>json</entry>
98739932
<entry>
98749933
Returns the array as JSON. A PostgreSQL multidimensional array
98759934
becomes a JSON array of arrays. Line feeds will be added between
@@ -9885,6 +9944,7 @@ table2-mapping
98859944
</indexterm>
98869945
<literal>row_to_json(record [, pretty_bool])</literal>
98879946
</entry>
9947+
<entry>json</entry>
98889948
<entry>
98899949
Returns the row as JSON. Line feeds will be added between level
98909950
1 elements if <parameter>pretty_bool</parameter> is true.
@@ -9899,6 +9959,7 @@ table2-mapping
98999959
</indexterm>
99009960
<literal>to_json(anyelement)</literal>
99019961
</entry>
9962+
<entry>json</entry>
99029963
<entry>
99039964
Returns the value as JSON. If the data type is not builtin, and there
99049965
is a cast from the type to json, the cast function will be used to
@@ -9909,6 +9970,182 @@ table2-mapping
99099970
<entry><literal>to_json('Fred said "Hi."'</literal></entry>
99109971
<entry><literal>"Fred said \"Hi.\""</literal></entry>
99119972
</row>
9973+
<row>
9974+
<entry>
9975+
<indexterm>
9976+
<primary>json_array_length</primary>
9977+
</indexterm>
9978+
<literal>json_array_length(json)</literal>
9979+
</entry>
9980+
<entry>int</entry>
9981+
<entry>
9982+
Returns the number of elements in the outermost json array.
9983+
</entry>
9984+
<entry><literal>json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</literal></entry>
9985+
<entry><literal>5</literal></entry>
9986+
</row>
9987+
<row>
9988+
<entry>
9989+
<indexterm>
9990+
<primary>json_each</primary>
9991+
</indexterm>
9992+
<literal>json_each(json)</literal>
9993+
</entry>
9994+
<entry>SETOF key text, value json</entry>
9995+
<entry>
9996+
Expands the outermost json object into a set of key/value pairs.
9997+
</entry>
9998+
<entry><literal>select * from json_each_as_text('{"a":"foo", "b":"bar"}')</literal></entry>
9999+
<entry>
10000+
<programlisting>
10001+
key | value
10002+
-----+-------
10003+
a | "foo"
10004+
b | "bar"
10005+
</programlisting>
10006+
</entry>
10007+
</row>
10008+
<row>
10009+
<entry>
10010+
<indexterm>
10011+
<primary>json_each_text</primary>
10012+
</indexterm>
10013+
<literal>json_each_text(from_json json)</literal>
10014+
</entry>
10015+
<entry>SETOF key text, value text</entry>
10016+
<entry>
10017+
Expands the outermost json object into a set of key/value pairs. The
10018+
returned value will be of type text.
10019+
</entry>
10020+
<entry><literal>select * from json_each_as_text('{"a":"foo", "b":"bar"}')</literal></entry>
10021+
<entry>
10022+
<programlisting>
10023+
key | value
10024+
-----+-------
10025+
a | foo
10026+
b | bar
10027+
</programlisting>
10028+
</entry>
10029+
</row>
10030+
<row>
10031+
<entry>
10032+
<indexterm>
10033+
<primary>json_extract_path</primary>
10034+
</indexterm>
10035+
<literal>json_extract_path(from_json json, VARIADIC path_elems text[])</literal>
10036+
</entry>
10037+
<entry>json</entry>
10038+
<entry>
10039+
Returns json object pointed to by <parameter>path_elems</parameter>.
10040+
</entry>
10041+
<entry><literal>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</literal></entry>
10042+
<entry><literal>{"f5":99,"f6":"foo"}</literal></entry>
10043+
</row>
10044+
<row>
10045+
<entry>
10046+
<indexterm>
10047+
<primary>json_extract_path_text</primary>
10048+
</indexterm>
10049+
<literal>json_extract_path_text(from_json json, VARIADIC path_elems text[])</literal>
10050+
</entry>
10051+
<entry>text</entry>
10052+
<entry>
10053+
Returns json object pointed to by <parameter>path_elems</parameter>.
10054+
</entry>
10055+
<entry><literal>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</literal></entry>
10056+
<entry><literal>foo</literal></entry>
10057+
</row>
10058+
<row>
10059+
<entry>
10060+
<indexterm>
10061+
<primary>json_object_keys</primary>
10062+
</indexterm>
10063+
<literal>json_object_keys(json)</literal>
10064+
</entry>
10065+
<entry>SETOF text</entry>
10066+
<entry>
10067+
Returns set of keys in the json object. Only the "outer" object will be displayed.
10068+
</entry>
10069+
<entry><literal>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</literal></entry>
10070+
<entry>
10071+
<programlisting>
10072+
json_object_keys
10073+
------------------
10074+
f1
10075+
f2
10076+
</programlisting>
10077+
</entry>
10078+
</row>
10079+
<row>
10080+
<entry>
10081+
<indexterm>
10082+
<primary>json_populate_record</primary>
10083+
</indexterm>
10084+
<literal>json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]</literal>
10085+
</entry>
10086+
<entry>anyelement</entry>
10087+
<entry>
10088+
Expands the object in from_json to a row whose columns match
10089+
the record type defined by base. Conversion will be best
10090+
effort; columns in base with no corresponding key in from_json
10091+
will be left null. A column may only be specified once.
10092+
</entry>
10093+
<entry><literal>json_populate_record(null::x, '{"a":1,"b":2}')</literal></entry>
10094+
<entry>
10095+
<programlisting>
10096+
a | b
10097+
---+---
10098+
1 | 2
10099+
</programlisting>
10100+
</entry>
10101+
</row>
10102+
<row>
10103+
<entry>
10104+
<indexterm>
10105+
<primary>json_populate_recordset</primary>
10106+
</indexterm>
10107+
<literal>json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]</literal>
10108+
</entry>
10109+
<entry>SETOF anyelement</entry>
10110+
<entry>
10111+
Expands the outermost set of objects in from_json to a set
10112+
whose columns match the record type defined by base.
10113+
Conversion will be best effort; columns in base with no
10114+
corresponding key in from_json will be left null. A column
10115+
may only be specified once.
10116+
</entry>
10117+
<entry><literal>json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')</literal></entry>
10118+
<entry>
10119+
<programlisting>
10120+
a | b
10121+
---+---
10122+
1 | 2
10123+
3 | 4
10124+
</programlisting>
10125+
</entry>
10126+
</row>
10127+
<row>
10128+
<entry>
10129+
<indexterm>
10130+
<primary>json_array_elements</primary>
10131+
</indexterm>
10132+
<literal>json_array_elements(json)</literal>
10133+
</entry>
10134+
<entry>SETOF json</entry>
10135+
<entry>
10136+
Expands a json array to a set of json elements.
10137+
</entry>
10138+
<entry><literal>json_array_elements('[1,true, [2,false]]')</literal></entry>
10139+
<entry>
10140+
<programlisting>
10141+
value
10142+
-----------
10143+
1
10144+
true
10145+
[2,false]
10146+
</programlisting>
10147+
</entry>
10148+
</row>
991210149
</tbody>
991310150
</tgroup>
991410151
</table>
@@ -9926,7 +10163,6 @@ table2-mapping
992610163
function <function>json_agg</function> which aggregates record
992710164
values as json efficiently.
992810165
</para>
9929-
993010166
</sect1>
993110167

993210168
<sect1 id="functions-sequence">

‎src/backend/catalog/system_views.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -787,3 +787,11 @@ COMMENT ON FUNCTION ts_debug(text) IS
787787
CREATEOR REPLACE FUNCTION
788788
pg_start_backup(labeltext, fastboolean DEFAULT false)
789789
RETURNStext STRICT VOLATILE LANGUAGE internalAS'pg_start_backup';
790+
791+
CREATEOR REPLACE FUNCTION
792+
json_populate_record(base anyelement, from_json json, use_json_as_textboolean DEFAULT false)
793+
RETURNS anyelement LANGUAGE internal STABLEAS'json_populate_record';
794+
795+
CREATEOR REPLACE FUNCTION
796+
json_populate_recordset(base anyelement, from_json json, use_json_as_textboolean DEFAULT false)
797+
RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS100AS'json_populate_recordset';

‎src/backend/utils/adt/Makefile‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -19,8 +19,8 @@ OBJS = acl.o arrayfuncs.o array_selfuncs.o array_typanalyze.o \
1919
array_userfuncs.o arrayutils.o bool.o\
2020
cash.o char.o date.o datetime.o datum.o domains.o\
2121
enum.o float.o format_type.o\
22-
geo_ops.o geo_selfuncs.o int.o int8.o json.olike.olockfuncs.o\
23-
misc.o nabstime.o name.o numeric.o numutils.o\
22+
geo_ops.o geo_selfuncs.o int.o int8.o json.ojsonfuncs.olike.o\
23+
lockfuncs.omisc.o nabstime.o name.o numeric.o numutils.o\
2424
oid.o oracle_compat.o pseudotypes.o rangetypes.o rangetypes_gist.o\
2525
rowtypes.o regexp.o regproc.o ruleutils.o selfuncs.o\
2626
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o\

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp