<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>JSON Functions and Operators</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.3.9 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Functions and Operators"
HREF="functions.html"><LINK
REL="PREVIOUS"
TITLE="XML Functions"
HREF="functions-xml.html"><LINK
REL="NEXT"
TITLE="Sequence Manipulation Functions"
HREF="functions-sequence.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2015-06-13T20:07:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.3.9 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="XML Functions"
HREF="functions-xml.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 9. Functions and Operators</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Sequence Manipulation Functions"
HREF="functions-sequence.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="FUNCTIONS-JSON"
>9.15. JSON Functions and Operators</A
></H1
><P
> <A
HREF="functions-json.html#FUNCTIONS-JSON-OP-TABLE"
>Table 9-40</A
> shows the operators that are
available for use with JSON (see <A
HREF="datatype-json.html"
>Section 8.14</A
>) data.
</P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-JSON-OP-TABLE"
></A
><P
><B
>Table 9-40. JSON Operators</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Operator</TH
><TH
>Right Operand Type</TH
><TH
>Description</TH
><TH
>Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>-></TT
></TD
><TD
>int</TD
><TD
>Get JSON array element</TD
><TD
><TT
CLASS="LITERAL"
>'[1,2,3]'::json->2</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>-></TT
></TD
><TD
>text</TD
><TD
>Get JSON object field</TD
><TD
><TT
CLASS="LITERAL"
>'{"a":1,"b":2}'::json->'b'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>->></TT
></TD
><TD
>int</TD
><TD
>Get JSON array element as text</TD
><TD
><TT
CLASS="LITERAL"
>'[1,2,3]'::json->>2</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>->></TT
></TD
><TD
>text</TD
><TD
>Get JSON object field as text</TD
><TD
><TT
CLASS="LITERAL"
>'{"a":1,"b":2}'::json->>'b'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>#></TT
></TD
><TD
>array of text</TD
><TD
>Get JSON object at specified path</TD
><TD
><TT
CLASS="LITERAL"
>'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>#>></TT
></TD
><TD
>array of text</TD
><TD
>Get JSON object at specified path as text</TD
><TD
><TT
CLASS="LITERAL"
>'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
> <A
HREF="functions-json.html#FUNCTIONS-JSON-TABLE"
>Table 9-41</A
> shows the functions that are available
for creating and manipulating JSON (see <A
HREF="datatype-json.html"
>Section 8.14</A
>) data.
</P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-JSON-TABLE"
></A
><P
><B
>Table 9-41. JSON Support Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Return Type</TH
><TH
>Description</TH
><TH
>Example</TH
><TH
>Example Result</TH
></TR
></THEAD
><TBODY
><TR
><TD
>
<TT
CLASS="LITERAL"
>array_to_json(anyarray [, pretty_bool])</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
> 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 <TT
CLASS="PARAMETER"
>pretty_bool</TT
> is true.
</TD
><TD
><TT
CLASS="LITERAL"
>array_to_json('{{1,5},{99,100}}'::int[])</TT
></TD
><TD
><TT
CLASS="LITERAL"
>[[1,5],[99,100]]</TT
></TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>row_to_json(record [, pretty_bool])</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
> Returns the row as JSON. Line feeds will be added between level
1 elements if <TT
CLASS="PARAMETER"
>pretty_bool</TT
> is true.
</TD
><TD
><TT
CLASS="LITERAL"
>row_to_json(row(1,'foo'))</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{"f1":1,"f2":"foo"}</TT
></TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>to_json(anyelement)</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
> Returns the value as JSON. If the data type is not built in, and there
is a cast from the type to <TT
CLASS="TYPE"
>json</TT
>, 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
><TT
CLASS="LITERAL"
>to_json('Fred said "Hi."'::text)</TT
></TD
><TD
><TT
CLASS="LITERAL"
>"Fred said \"Hi.\""</TT
></TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_array_length(json)</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>int</TT
></TD
><TD
> Returns the number of elements in the outermost JSON array.
</TD
><TD
><TT
CLASS="LITERAL"
>json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>5</TT
></TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_each(json)</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>SETOF key text, value json</TT
></TD
><TD
> Expands the outermost JSON object into a set of key/value pairs.
</TD
><TD
><TT
CLASS="LITERAL"
>select * from json_each('{"a":"foo", "b":"bar"}')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> key | value
-----+-------
a | "foo"
b | "bar"
</PRE
>
</TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_each_text(from_json json)</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>SETOF key text, value text</TT
></TD
><TD
> Expands the outermost JSON object into a set of key/value pairs. The
returned value will be of type text.
</TD
><TD
><TT
CLASS="LITERAL"
>select * from json_each_text('{"a":"foo", "b":"bar"}')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> key | value
-----+-------
a | foo
b | bar
</PRE
>
</TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_extract_path(from_json json, VARIADIC path_elems text[])</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
> Returns JSON object pointed to by <TT
CLASS="PARAMETER"
>path_elems</TT
>.
</TD
><TD
><TT
CLASS="LITERAL"
>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>{"f5":99,"f6":"foo"}</TT
></TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_extract_path_text(from_json json, VARIADIC path_elems text[])</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
> Returns JSON object pointed to by <TT
CLASS="PARAMETER"
>path_elems</TT
>.
</TD
><TD
><TT
CLASS="LITERAL"
>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>foo</TT
></TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_object_keys(json)</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>SETOF text</TT
></TD
><TD
> Returns set of keys in the JSON object. Only the <SPAN
CLASS="QUOTE"
>"outer"</SPAN
> object will be displayed.
</TD
><TD
><TT
CLASS="LITERAL"
>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> json_object_keys
------------------
f1
f2</PRE
>
</TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>anyelement</TT
></TD
><TD
> Expands the object in <TT
CLASS="REPLACEABLE"
><I
>from_json</I
></TT
> 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 <TT
CLASS="REPLACEABLE"
><I
>from_json</I
></TT
>
will be left null. If a column is specified more than once, the last value is used.
</TD
><TD
><TT
CLASS="LITERAL"
>select * from json_populate_record(null::x, '{"a":1,"b":2}')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> a | b
---+---
1 | 2</PRE
>
</TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>SETOF anyelement</TT
></TD
><TD
> Expands the outermost set of objects in <TT
CLASS="REPLACEABLE"
><I
>from_json</I
></TT
> 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 <TT
CLASS="REPLACEABLE"
><I
>from_json</I
></TT
> will be left null.
If a column is specified more than once, the last value is used.
</TD
><TD
><TT
CLASS="LITERAL"
>select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> a | b
---+---
1 | 2
3 | 4
</PRE
>
</TD
></TR
><TR
><TD
>
<TT
CLASS="LITERAL"
>json_array_elements(json)</TT
>
</TD
><TD
><TT
CLASS="TYPE"
>SETOF json</TT
></TD
><TD
> Expands a JSON array to a set of JSON elements.
</TD
><TD
><TT
CLASS="LITERAL"
>json_array_elements('[1,true, [2,false]]')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> value
-----------
1
true
[2,false]</PRE
>
</TD
></TR
></TBODY
></TABLE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> The <TT
CLASS="TYPE"
>json</TT
> 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.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> 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.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> The <A
HREF="hstore.html"
>hstore</A
> extension has a cast from <TT
CLASS="TYPE"
>hstore</TT
> to
<TT
CLASS="TYPE"
>json</TT
>, so that converted <TT
CLASS="TYPE"
>hstore</TT
> values are represented as JSON objects,
not as string values.
</P
></BLOCKQUOTE
></DIV
><P
> See also <A
HREF="functions-aggregate.html"
>Section 9.20</A
> about the aggregate
function <CODE
CLASS="FUNCTION"
>json_agg</CODE
> which aggregates record
values as JSON efficiently.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-xml.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-sequence.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>XML Functions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Sequence Manipulation Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
