json.html
text/html
Filename: json.html
Type: text/html
Part: 0
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>json</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.1devel Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Additional Supplied Modules"
HREF="contrib.html"><LINK
REL="PREVIOUS"
TITLE="isn"
HREF="isn.html"><LINK
REL="NEXT"
TITLE="lo"
HREF="lo.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="2010-07-23T03:47:28"></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.1devel Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="isn"
HREF="isn.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Additional Supplied Modules"
HREF="contrib.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Appendix F. Additional Supplied Modules</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Additional Supplied Modules"
HREF="contrib.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
TITLE="lo"
HREF="lo.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="JSON"
>F.17. json</A
></H1
><P
> This module implements the <TT
CLASS="TYPE"
>json</TT
> data type for storing <A
HREF="http://www.json.org/"
TARGET="_top"
>JSON</A
> content in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>. The advantage of using the <TT
CLASS="TYPE"
>json</TT
> type over storing JSON content in a <TT
CLASS="TYPE"
>text</TT
> field is that it makes sure input values are valid JSON, and there are several type-safe functions for manipulating JSON content.
</P
><P
> The <TT
CLASS="TYPE"
>json</TT
> type stores valid JSON <SPAN
CLASS="QUOTE"
>"values"</SPAN
> as defined by <A
HREF="http://json.org/"
TARGET="_top"
>json.org</A
>. That is, a <TT
CLASS="TYPE"
>json</TT
> field can hold a string, number, object, array, 'true', 'false', or 'null'.
</P
><P
> The <TT
CLASS="TYPE"
>json</TT
> datatype should be thought of as a specialization of <TT
CLASS="TYPE"
>text</TT
> rather than a wrapper around <TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>int</TT
>, <TT
CLASS="TYPE"
>float</TT
>, etc. For instance, <TT
CLASS="LITERAL"
>' "string" '::json::text</TT
> will simply yield <TT
CLASS="LITERAL"
>' "string" '</TT
>. Also, bear in mind that JSON null (<TT
CLASS="LITERAL"
>'null'::json</TT
>) and SQL NULL (<TT
CLASS="LITERAL"
>NULL::json</TT
>) are two different things.
</P
><P
> The json module is currently under development.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN120233"
>F.17.1. <TT
CLASS="TYPE"
>json</TT
> Functions</A
></H2
><DIV
CLASS="TABLE"
><A
NAME="JSON-FUNC-TABLE"
></A
><P
><B
>Table F-11. <TT
CLASS="TYPE"
>json</TT
> 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
>Result</TH
></TR
></THEAD
><TBODY
><TR
><TD
ROWSPAN="2"
><CODE
CLASS="FUNCTION"
>to_json(anyelement)</CODE
></TD
><TD
ROWSPAN="2"
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
ROWSPAN="2"
>Encode a value as JSON.</TD
><TD
><TT
CLASS="LITERAL"
>to_json('string'::TEXT)</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'"string"'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_json(array['one','two','three',null]::text[])</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'["one","two","three",null]'</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>from_json(json)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>Decode a JSON-encoded value.</TD
><TD
><TT
CLASS="LITERAL"
>from_json('"string"')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'string'</TT
></TD
></TR
><TR
><TD
ROWSPAN="2"
><CODE
CLASS="FUNCTION"
>json_validate(text)</CODE
></TD
><TD
ROWSPAN="2"
><TT
CLASS="TYPE"
>boolean</TT
></TD
><TD
ROWSPAN="2"
>Determine if text is valid JSON.</TD
><TD
><TT
CLASS="LITERAL"
>json_validate('{key: "value"}')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>false</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_validate('{"key": "value"}')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>true</TT
></TD
></TR
><TR
><TD
ROWSPAN="3"
><CODE
CLASS="FUNCTION"
>json_get(json, jsonpath text)</CODE
></TD
><TD
ROWSPAN="3"
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
ROWSPAN="3"
>Select a single value from a JSON tree using a JSONPath expression.</TD
><TD
><TT
CLASS="LITERAL"
>json_get('[0,1,2]', '$[1]')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'1'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_get('[0,1,2]', '$[100]')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>NULL</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_get('[0,1,2]', '$[*]')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>Error</TT
></TD
></TR
><TR
><TD
ROWSPAN="3"
><CODE
CLASS="FUNCTION"
>json_set(json, jsonpath text, json)</CODE
></TD
><TD
ROWSPAN="3"
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
ROWSPAN="3"
>Set items in a JSON tree that match a JSONPath expression.</TD
><TD
><TT
CLASS="LITERAL"
>json_set('[0,1,2]', '$[1]', '"x"')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'[0,"x",2]'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_set('[0,1,2]', '$[100]', '"x"')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'[0,1,2]'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_set('[0,1,2]', '$[*]', '"x"')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'["x","x","x"]'</TT
></TD
></TR
><TR
><TD
ROWSPAN="3"
><CODE
CLASS="FUNCTION"
>json_path(json, jsonpath text)</CODE
></TD
><TD
ROWSPAN="3"
><TT
CLASS="TYPE"
>setof json</TT
></TD
><TD
ROWSPAN="3"
>Select multiple values from a JSON tree using a JSONPath expression.</TD
><TD
><TT
CLASS="LITERAL"
>json_path('[0,1,2]', '$[1]')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> 1
(1 row)</PRE
>
</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_path('[0,1,2]', '$[100]')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
>(0 rows)</PRE
>
</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_path('[0,1,2]', '$[*]')</TT
></TD
><TD
><PRE
CLASS="PROGRAMLISTING"
> 0
1
2
(3 rows)</PRE
>
</TD
></TR
><TR
><TD
ROWSPAN="2"
><CODE
CLASS="FUNCTION"
>json_condense(json)</CODE
></TD
><TD
ROWSPAN="2"
><TT
CLASS="TYPE"
>json</TT
></TD
><TD
ROWSPAN="2"
>Re-encodes JSON to form a string with minimal length (mainly removes whitespace).</TD
><TD
><TT
CLASS="LITERAL"
>json_condense(' { "key" : "value"} ')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'{"key":"value"}'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>json_condense($$ "\u266B" $$)</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'"♫"' -- if encoding supports Unicode</TT
></TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>json_type(json)</CODE
></TD
><TD
><TT
CLASS="TYPE"
>json_type_t</TT
> - one of:
<PRE
CLASS="PROGRAMLISTING"
>'null'
'string'
'number'
'bool'
'object'
'array'</PRE
>
</TD
><TD
>Get the type of a <TT
CLASS="TYPE"
>json</TT
> value.</TD
><TD
><TT
CLASS="LITERAL"
>json_type('{"pi": "3.14159", "e": "2.71828"}')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'object'</TT
></TD
></TR
></TBODY
></TABLE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN120375"
>F.17.2. Author</A
></H2
><P
> Joey Adams <CODE
CLASS="EMAIL"
><<A
HREF="mailto:joeyadams3.14159@gmail.com"
>joeyadams3.14159@gmail.com</A
>></CODE
>
</P
><P
> Development of this module was sponsored by Google through its Google Summer of Code program (<A
HREF="http://code.google.com/soc"
TARGET="_top"
>code.google.com/soc</A
>).
</P
></DIV
></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="isn.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="lo.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>isn</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="contrib.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>lo</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>