Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-json.html1927 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.16. JSON Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-xml.html" title="9.15. XML Functions" /><link rel="next" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.16. JSON Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-xml.html" title="9.15. XML Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-JSON"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.16. JSON Functions and Operators <a href="#FUNCTIONS-JSON" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-json.html#FUNCTIONS-JSON-PROCESSING">9.16.1. Processing and Creating JSON Data</a></span></dt><dt><span class="sect2"><a href="functions-json.html#FUNCTIONS-SQLJSON-PATH">9.16.2. The SQL/JSON Path Language</a></span></dt></dl></div><a id="id-1.5.8.22.2" class="indexterm"></a><a id="id-1.5.8.22.3" class="indexterm"></a><p>3   This section describes:4 5   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>6      functions and operators for processing and creating JSON data7     </p></li><li class="listitem"><p>8      the SQL/JSON path language9     </p></li></ul></div><p>10  </p><p>11   To provide native support for JSON data types within the SQL environment,12   <span class="productname">PostgreSQL</span> implements the13   <em class="firstterm">SQL/JSON data model</em>.14   This model comprises sequences of items. Each item can hold SQL scalar15   values, with an additional SQL/JSON null value, and composite data structures16   that use JSON arrays and objects. The model is a formalization of the implied17   data model in the JSON specification18   <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc7159" target="_top">RFC 7159</a>.19  </p><p>20   SQL/JSON allows you to handle JSON data alongside regular SQL data,21   with transaction support, including:22 23  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>24     Uploading JSON data into the database and storing it in25     regular SQL columns as character or binary strings.26    </p></li><li class="listitem"><p>27     Generating JSON objects and arrays from relational data.28    </p></li><li class="listitem"><p>29     Querying JSON data using SQL/JSON query functions and30     SQL/JSON path language expressions.31    </p></li></ul></div><p>32  </p><p>33   To learn more about the SQL/JSON standard, see34   <a class="xref" href="biblio.html#SQLTR-19075-6" title="SQL Technical Report">[sqltr-19075-6]</a>. For details on JSON types35   supported in <span class="productname">PostgreSQL</span>,36   see <a class="xref" href="datatype-json.html" title="8.14. JSON Types">Section 8.14</a>.37  </p><div class="sect2" id="FUNCTIONS-JSON-PROCESSING"><div class="titlepage"><div><div><h3 class="title">9.16.1. Processing and Creating JSON Data <a href="#FUNCTIONS-JSON-PROCESSING" class="id_link">#</a></h3></div></div></div><p>38   <a class="xref" href="functions-json.html#FUNCTIONS-JSON-OP-TABLE" title="Table 9.45. json and jsonb Operators">Table 9.45</a> shows the operators that39   are available for use with JSON data types (see <a class="xref" href="datatype-json.html" title="8.14. JSON Types">Section 8.14</a>).40   In addition, the usual comparison operators shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are available for41   <code class="type">jsonb</code>, though not for <code class="type">json</code>.  The comparison42   operators follow the ordering rules for B-tree operations outlined in43   <a class="xref" href="datatype-json.html#JSON-INDEXING" title="8.14.4. jsonb Indexing">Section 8.14.4</a>.44   See also <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for the aggregate45   function <code class="function">json_agg</code> which aggregates record46   values as JSON, the aggregate function47   <code class="function">json_object_agg</code> which aggregates pairs of values48   into a JSON object, and their <code class="type">jsonb</code> equivalents,49   <code class="function">jsonb_agg</code> and <code class="function">jsonb_object_agg</code>.50  </p><div class="table" id="FUNCTIONS-JSON-OP-TABLE"><p class="title"><strong>Table 9.45. <code class="type">json</code> and <code class="type">jsonb</code> Operators</strong></p><div class="table-contents"><table class="table" summary="json and jsonb Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">51        Operator52       </p>53       <p>54        Description55       </p>56       <p>57        Example(s)58       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">59        <code class="type">json</code> <code class="literal">-&gt;</code> <code class="type">integer</code>60        → <code class="returnvalue">json</code>61       </p>62       <p class="func_signature">63        <code class="type">jsonb</code> <code class="literal">-&gt;</code> <code class="type">integer</code>64        → <code class="returnvalue">jsonb</code>65       </p>66       <p>67        Extracts <em class="parameter"><code>n</code></em>'th element of JSON array68        (array elements are indexed from zero, but negative integers count69        from the end).70       </p>71       <p>72        <code class="literal">'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -&gt; 2</code>73        → <code class="returnvalue">{"c":"baz"}</code>74       </p>75       <p>76        <code class="literal">'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -&gt; -3</code>77        → <code class="returnvalue">{"a":"foo"}</code>78       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">79        <code class="type">json</code> <code class="literal">-&gt;</code> <code class="type">text</code>80        → <code class="returnvalue">json</code>81       </p>82       <p class="func_signature">83        <code class="type">jsonb</code> <code class="literal">-&gt;</code> <code class="type">text</code>84        → <code class="returnvalue">jsonb</code>85       </p>86       <p>87        Extracts JSON object field with the given key.88       </p>89       <p>90        <code class="literal">'{"a": {"b":"foo"}}'::json -&gt; 'a'</code>91        → <code class="returnvalue">{"b":"foo"}</code>92       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">93        <code class="type">json</code> <code class="literal">-&gt;&gt;</code> <code class="type">integer</code>94        → <code class="returnvalue">text</code>95       </p>96       <p class="func_signature">97        <code class="type">jsonb</code> <code class="literal">-&gt;&gt;</code> <code class="type">integer</code>98        → <code class="returnvalue">text</code>99       </p>100       <p>101        Extracts <em class="parameter"><code>n</code></em>'th element of JSON array,102        as <code class="type">text</code>.103       </p>104       <p>105        <code class="literal">'[1,2,3]'::json -&gt;&gt; 2</code>106        → <code class="returnvalue">3</code>107       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">108        <code class="type">json</code> <code class="literal">-&gt;&gt;</code> <code class="type">text</code>109        → <code class="returnvalue">text</code>110       </p>111       <p class="func_signature">112        <code class="type">jsonb</code> <code class="literal">-&gt;&gt;</code> <code class="type">text</code>113        → <code class="returnvalue">text</code>114       </p>115       <p>116        Extracts JSON object field with the given key, as <code class="type">text</code>.117       </p>118       <p>119        <code class="literal">'{"a":1,"b":2}'::json -&gt;&gt; 'b'</code>120        → <code class="returnvalue">2</code>121       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">122        <code class="type">json</code> <code class="literal">#&gt;</code> <code class="type">text[]</code>123        → <code class="returnvalue">json</code>124       </p>125       <p class="func_signature">126        <code class="type">jsonb</code> <code class="literal">#&gt;</code> <code class="type">text[]</code>127        → <code class="returnvalue">jsonb</code>128       </p>129       <p>130        Extracts JSON sub-object at the specified path, where path elements131        can be either field keys or array indexes.132       </p>133       <p>134        <code class="literal">'{"a": {"b": ["foo","bar"]}}'::json #&gt; '{a,b,1}'</code>135        → <code class="returnvalue">"bar"</code>136       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">137        <code class="type">json</code> <code class="literal">#&gt;&gt;</code> <code class="type">text[]</code>138        → <code class="returnvalue">text</code>139       </p>140       <p class="func_signature">141        <code class="type">jsonb</code> <code class="literal">#&gt;&gt;</code> <code class="type">text[]</code>142        → <code class="returnvalue">text</code>143       </p>144       <p>145        Extracts JSON sub-object at the specified path as <code class="type">text</code>.146       </p>147       <p>148        <code class="literal">'{"a": {"b": ["foo","bar"]}}'::json #&gt;&gt; '{a,b,1}'</code>149        → <code class="returnvalue">bar</code>150       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>151    The field/element/path extraction operators return NULL, rather than152    failing, if the JSON input does not have the right structure to match153    the request; for example if no such key or array element exists.154   </p></div><p>155   Some further operators exist only for <code class="type">jsonb</code>, as shown156   in <a class="xref" href="functions-json.html#FUNCTIONS-JSONB-OP-TABLE" title="Table 9.46. Additional jsonb Operators">Table 9.46</a>.157   <a class="xref" href="datatype-json.html#JSON-INDEXING" title="8.14.4. jsonb Indexing">Section 8.14.4</a>158   describes how these operators can be used to effectively search indexed159   <code class="type">jsonb</code> data.160  </p><div class="table" id="FUNCTIONS-JSONB-OP-TABLE"><p class="title"><strong>Table 9.46. Additional <code class="type">jsonb</code> Operators</strong></p><div class="table-contents"><table class="table" summary="Additional jsonb Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">161        Operator162       </p>163       <p>164        Description165       </p>166       <p>167        Example(s)168       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">169        <code class="type">jsonb</code> <code class="literal">@&gt;</code> <code class="type">jsonb</code>170        → <code class="returnvalue">boolean</code>171       </p>172       <p>173        Does the first JSON value contain the second?174        (See <a class="xref" href="datatype-json.html#JSON-CONTAINMENT" title="8.14.3. jsonb Containment and Existence">Section 8.14.3</a> for details about containment.)175       </p>176       <p>177        <code class="literal">'{"a":1, "b":2}'::jsonb @&gt; '{"b":2}'::jsonb</code>178        → <code class="returnvalue">t</code>179       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">180        <code class="type">jsonb</code> <code class="literal">&lt;@</code> <code class="type">jsonb</code>181        → <code class="returnvalue">boolean</code>182       </p>183       <p>184        Is the first JSON value contained in the second?185       </p>186       <p>187        <code class="literal">'{"b":2}'::jsonb &lt;@ '{"a":1, "b":2}'::jsonb</code>188        → <code class="returnvalue">t</code>189       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">190        <code class="type">jsonb</code> <code class="literal">?</code> <code class="type">text</code>191        → <code class="returnvalue">boolean</code>192       </p>193       <p>194        Does the text string exist as a top-level key or array element within195        the JSON value?196       </p>197       <p>198        <code class="literal">'{"a":1, "b":2}'::jsonb ? 'b'</code>199        → <code class="returnvalue">t</code>200       </p>201       <p>202        <code class="literal">'["a", "b", "c"]'::jsonb ? 'b'</code>203        → <code class="returnvalue">t</code>204       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">205        <code class="type">jsonb</code> <code class="literal">?|</code> <code class="type">text[]</code>206        → <code class="returnvalue">boolean</code>207       </p>208       <p>209        Do any of the strings in the text array exist as top-level keys or210        array elements?211       </p>212       <p>213        <code class="literal">'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']</code>214        → <code class="returnvalue">t</code>215       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">216        <code class="type">jsonb</code> <code class="literal">?&amp;</code> <code class="type">text[]</code>217        → <code class="returnvalue">boolean</code>218       </p>219       <p>220        Do all of the strings in the text array exist as top-level keys or221        array elements?222       </p>223       <p>224        <code class="literal">'["a", "b", "c"]'::jsonb ?&amp; array['a', 'b']</code>225        → <code class="returnvalue">t</code>226       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">227        <code class="type">jsonb</code> <code class="literal">||</code> <code class="type">jsonb</code>228        → <code class="returnvalue">jsonb</code>229       </p>230       <p>231        Concatenates two <code class="type">jsonb</code> values.232        Concatenating two arrays generates an array containing all the233        elements of each input.  Concatenating two objects generates an234        object containing the union of their235        keys, taking the second object's value when there are duplicate keys.236        All other cases are treated by converting a non-array input into a237        single-element array, and then proceeding as for two arrays.238        Does not operate recursively: only the top-level array or object239        structure is merged.240       </p>241       <p>242        <code class="literal">'["a", "b"]'::jsonb || '["a", "d"]'::jsonb</code>243        → <code class="returnvalue">["a", "b", "a", "d"]</code>244       </p>245       <p>246        <code class="literal">'{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb</code>247        → <code class="returnvalue">{"a": "b", "c": "d"}</code>248       </p>249       <p>250        <code class="literal">'[1, 2]'::jsonb || '3'::jsonb</code>251        → <code class="returnvalue">[1, 2, 3]</code>252       </p>253       <p>254        <code class="literal">'{"a": "b"}'::jsonb || '42'::jsonb</code>255        → <code class="returnvalue">[{"a": "b"}, 42]</code>256       </p>257       <p>258        To append an array to another array as a single entry, wrap it259        in an additional layer of array, for example:260       </p>261       <p>262        <code class="literal">'[1, 2]'::jsonb || jsonb_build_array('[3, 4]'::jsonb)</code>263        → <code class="returnvalue">[1, 2, [3, 4]]</code>264       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">265        <code class="type">jsonb</code> <code class="literal">-</code> <code class="type">text</code>266        → <code class="returnvalue">jsonb</code>267       </p>268       <p>269        Deletes a key (and its value) from a JSON object, or matching string270        value(s) from a JSON array.271       </p>272       <p>273        <code class="literal">'{"a": "b", "c": "d"}'::jsonb - 'a'</code>274        → <code class="returnvalue">{"c": "d"}</code>275       </p>276       <p>277        <code class="literal">'["a", "b", "c", "b"]'::jsonb - 'b'</code>278        → <code class="returnvalue">["a", "c"]</code>279       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">280        <code class="type">jsonb</code> <code class="literal">-</code> <code class="type">text[]</code>281        → <code class="returnvalue">jsonb</code>282       </p>283       <p>284        Deletes all matching keys or array elements from the left operand.285       </p>286       <p>287        <code class="literal">'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]</code>288        → <code class="returnvalue">{}</code>289       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">290        <code class="type">jsonb</code> <code class="literal">-</code> <code class="type">integer</code>291        → <code class="returnvalue">jsonb</code>292       </p>293       <p>294        Deletes the array element with specified index (negative295        integers count from the end).  Throws an error if JSON value296        is not an array.297       </p>298       <p>299        <code class="literal">'["a", "b"]'::jsonb - 1 </code>300        → <code class="returnvalue">["a"]</code>301       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">302        <code class="type">jsonb</code> <code class="literal">#-</code> <code class="type">text[]</code>303        → <code class="returnvalue">jsonb</code>304       </p>305       <p>306        Deletes the field or array element at the specified path, where path307        elements can be either field keys or array indexes.308       </p>309       <p>310        <code class="literal">'["a", {"b":1}]'::jsonb #- '{1,b}'</code>311        → <code class="returnvalue">["a", {}]</code>312       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">313        <code class="type">jsonb</code> <code class="literal">@?</code> <code class="type">jsonpath</code>314        → <code class="returnvalue">boolean</code>315       </p>316       <p>317        Does JSON path return any item for the specified JSON value?318       </p>319       <p>320        <code class="literal">'{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ &gt; 2)'</code>321        → <code class="returnvalue">t</code>322       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">323        <code class="type">jsonb</code> <code class="literal">@@</code> <code class="type">jsonpath</code>324        → <code class="returnvalue">boolean</code>325       </p>326       <p>327        Returns the result of a JSON path predicate check for the328        specified JSON value.  Only the first item of the result is taken into329        account.  If the result is not Boolean, then <code class="literal">NULL</code>330        is returned.331       </p>332       <p>333        <code class="literal">'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] &gt; 2'</code>334        → <code class="returnvalue">t</code>335       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>336    The <code class="type">jsonpath</code> operators <code class="literal">@?</code>337    and <code class="literal">@@</code> suppress the following errors: missing object338    field or array element, unexpected JSON item type, datetime and numeric339    errors.  The <code class="type">jsonpath</code>-related functions described below can340    also be told to suppress these types of errors.  This behavior might be341    helpful when searching JSON document collections of varying structure.342   </p></div><p>343   <a class="xref" href="functions-json.html#FUNCTIONS-JSON-CREATION-TABLE" title="Table 9.47. JSON Creation Functions">Table 9.47</a> shows the functions that are344   available for constructing <code class="type">json</code> and <code class="type">jsonb</code> values.345   Some functions in this table have a <code class="literal">RETURNING</code> clause,346   which specifies the data type returned.  It must be one of <code class="type">json</code>,347   <code class="type">jsonb</code>, <code class="type">bytea</code>, a character string type (<code class="type">text</code>,348   <code class="type">char</code>, or <code class="type">varchar</code>), or a type349   for which there is a cast from <code class="type">json</code> to that type.350   By default, the <code class="type">json</code> type is returned.351  </p><div class="table" id="FUNCTIONS-JSON-CREATION-TABLE"><p class="title"><strong>Table 9.47. JSON Creation Functions</strong></p><div class="table-contents"><table class="table" summary="JSON Creation Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">352        Function353       </p>354       <p>355        Description356       </p>357       <p>358        Example(s)359       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">360        <a id="id-1.5.8.22.8.9.2.2.1.1.1.1" class="indexterm"></a>361        <code class="function">to_json</code> ( <code class="type">anyelement</code> )362        → <code class="returnvalue">json</code>363       </p>364       <p class="func_signature">365        <a id="id-1.5.8.22.8.9.2.2.1.1.2.1" class="indexterm"></a>366        <code class="function">to_jsonb</code> ( <code class="type">anyelement</code> )367        → <code class="returnvalue">jsonb</code>368       </p>369       <p>370        Converts any SQL value to <code class="type">json</code> or <code class="type">jsonb</code>.371        Arrays and composites are converted recursively to arrays and372        objects (multidimensional arrays become arrays of arrays in JSON).373        Otherwise, if there is a cast from the SQL data type374        to <code class="type">json</code>, the cast function will be used to perform the375        conversion;<a href="#ftn.id-1.5.8.22.8.9.2.2.1.1.3.4" class="footnote"><sup class="footnote" id="id-1.5.8.22.8.9.2.2.1.1.3.4">[a]</sup></a>376        otherwise, a scalar JSON value is produced.  For any scalar other than377        a number, a Boolean, or a null value, the text representation will be378        used, with escaping as necessary to make it a valid JSON string value.379       </p>380       <p>381        <code class="literal">to_json('Fred said "Hi."'::text)</code>382        → <code class="returnvalue">"Fred said \"Hi.\""</code>383       </p>384       <p>385        <code class="literal">to_jsonb(row(42, 'Fred said "Hi."'::text))</code>386        → <code class="returnvalue">{"f1": 42, "f2": "Fred said \"Hi.\""}</code>387       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">388        <a id="id-1.5.8.22.8.9.2.2.2.1.1.1" class="indexterm"></a>389        <code class="function">array_to_json</code> ( <code class="type">anyarray</code> [<span class="optional">, <code class="type">boolean</code> </span>] )390        → <code class="returnvalue">json</code>391       </p>392       <p>393        Converts an SQL array to a JSON array.  The behavior is the same394        as <code class="function">to_json</code> except that line feeds will be added395        between top-level array elements if the optional boolean parameter is396        true.397       </p>398       <p>399        <code class="literal">array_to_json('{{1,5},{99,100}}'::int[])</code>400        → <code class="returnvalue">[[1,5],[99,100]]</code>401       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">402        <a id="id-1.5.8.22.8.9.2.2.3.1.1.1" class="indexterm"></a>403         <code class="function">json_array</code> (404         [<span class="optional"> { <em class="replaceable"><code>value_expression</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> </span>] } [<span class="optional">, ...</span>] </span>]405         [<span class="optional"> { <code class="literal">NULL</code> | <code class="literal">ABSENT</code> } <code class="literal">ON NULL</code> </span>]406         [<span class="optional"> <code class="literal">RETURNING</code> <em class="replaceable"><code>data_type</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> [<span class="optional"> <code class="literal">ENCODING UTF8</code> </span>] </span>] </span>])407        </p>408        <p class="func_signature">409         <code class="function">json_array</code> (410         [<span class="optional"> <em class="replaceable"><code>query_expression</code></em> </span>]411         [<span class="optional"> <code class="literal">RETURNING</code> <em class="replaceable"><code>data_type</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> [<span class="optional"> <code class="literal">ENCODING UTF8</code> </span>] </span>] </span>])412        </p>413        <p>414         Constructs a JSON array from either a series of415         <em class="replaceable"><code>value_expression</code></em> parameters or from the results416         of <em class="replaceable"><code>query_expression</code></em>,417         which must be a SELECT query returning a single column. If418         <code class="literal">ABSENT ON NULL</code> is specified, NULL values are ignored.419         This is always the case if a420         <em class="replaceable"><code>query_expression</code></em> is used.421        </p>422        <p>423         <code class="literal">json_array(1,true,json '{"a":null}')</code>424         → <code class="returnvalue">[1, true, {"a":null}]</code>425        </p>426        <p>427         <code class="literal">json_array(SELECT * FROM (VALUES(1),(2)) t)</code>428         → <code class="returnvalue">[1, 2]</code>429       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">430        <a id="id-1.5.8.22.8.9.2.2.4.1.1.1" class="indexterm"></a>431        <code class="function">row_to_json</code> ( <code class="type">record</code> [<span class="optional">, <code class="type">boolean</code> </span>] )432        → <code class="returnvalue">json</code>433       </p>434       <p>435        Converts an SQL composite value to a JSON object.  The behavior is the436        same as <code class="function">to_json</code> except that line feeds will be437        added between top-level elements if the optional boolean parameter is438        true.439       </p>440       <p>441        <code class="literal">row_to_json(row(1,'foo'))</code>442        → <code class="returnvalue">{"f1":1,"f2":"foo"}</code>443       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">444        <a id="id-1.5.8.22.8.9.2.2.5.1.1.1" class="indexterm"></a>445        <code class="function">json_build_array</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )446        → <code class="returnvalue">json</code>447       </p>448       <p class="func_signature">449        <a id="id-1.5.8.22.8.9.2.2.5.1.2.1" class="indexterm"></a>450        <code class="function">jsonb_build_array</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )451        → <code class="returnvalue">jsonb</code>452       </p>453       <p>454        Builds a possibly-heterogeneously-typed JSON array out of a variadic455        argument list.  Each argument is converted as456        per <code class="function">to_json</code> or <code class="function">to_jsonb</code>.457       </p>458       <p>459        <code class="literal">json_build_array(1, 2, 'foo', 4, 5)</code>460        → <code class="returnvalue">[1, 2, "foo", 4, 5]</code>461       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">462        <a id="id-1.5.8.22.8.9.2.2.6.1.1.1" class="indexterm"></a>463        <code class="function">json_build_object</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )464        → <code class="returnvalue">json</code>465       </p>466       <p class="func_signature">467        <a id="id-1.5.8.22.8.9.2.2.6.1.2.1" class="indexterm"></a>468        <code class="function">jsonb_build_object</code> ( <code class="literal">VARIADIC</code> <code class="type">"any"</code> )469        → <code class="returnvalue">jsonb</code>470       </p>471       <p>472        Builds a JSON object out of a variadic argument list.  By convention,473        the argument list consists of alternating keys and values.  Key474        arguments are coerced to text; value arguments are converted as475        per <code class="function">to_json</code> or <code class="function">to_jsonb</code>.476       </p>477       <p>478        <code class="literal">json_build_object('foo', 1, 2, row(3,'bar'))</code>479        → <code class="returnvalue">{"foo" : 1, "2" : {"f1":3,"f2":"bar"}}</code>480       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">481         <a id="id-1.5.8.22.8.9.2.2.7.1.1.1" class="indexterm"></a>482         <code class="function">json_object</code> (483         [<span class="optional"> { <em class="replaceable"><code>key_expression</code></em> { <code class="literal">VALUE</code> | ':' }484          <em class="replaceable"><code>value_expression</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> [<span class="optional"> <code class="literal">ENCODING UTF8</code> </span>] </span>] }[<span class="optional">, ...</span>] </span>]485         [<span class="optional"> { <code class="literal">NULL</code> | <code class="literal">ABSENT</code> } <code class="literal">ON NULL</code> </span>]486         [<span class="optional"> { <code class="literal">WITH</code> | <code class="literal">WITHOUT</code> } <code class="literal">UNIQUE</code> [<span class="optional"> <code class="literal">KEYS</code> </span>] </span>]487         [<span class="optional"> <code class="literal">RETURNING</code> <em class="replaceable"><code>data_type</code></em> [<span class="optional"> <code class="literal">FORMAT JSON</code> [<span class="optional"> <code class="literal">ENCODING UTF8</code> </span>] </span>] </span>])488        </p>489        <p>490         Constructs a JSON object of all the key/value pairs given,491         or an empty object if none are given.492         <em class="replaceable"><code>key_expression</code></em> is a scalar expression493         defining the <acronym class="acronym">JSON</acronym> key, which is494         converted to the <code class="type">text</code> type.495         It cannot be <code class="literal">NULL</code> nor can it496         belong to a type that has a cast to the <code class="type">json</code> type.497         If <code class="literal">WITH UNIQUE KEYS</code> is specified, there must not498         be any duplicate <em class="replaceable"><code>key_expression</code></em>.499         Any pair for which the <em class="replaceable"><code>value_expression</code></em>500         evaluates to <code class="literal">NULL</code> is omitted from the output501         if <code class="literal">ABSENT ON NULL</code> is specified;502         if <code class="literal">NULL ON NULL</code> is specified or the clause503         omitted, the key is included with value <code class="literal">NULL</code>.504        </p>505        <p>506         <code class="literal">json_object('code' VALUE 'P123', 'title': 'Jaws')</code>507         → <code class="returnvalue">{"code" : "P123", "title" : "Jaws"}</code>508       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">509        <a id="id-1.5.8.22.8.9.2.2.8.1.1.1" class="indexterm"></a>510        <code class="function">json_object</code> ( <code class="type">text[]</code> )511        → <code class="returnvalue">json</code>512       </p>513       <p class="func_signature">514        <a id="id-1.5.8.22.8.9.2.2.8.1.2.1" class="indexterm"></a>515        <code class="function">jsonb_object</code> ( <code class="type">text[]</code> )516        → <code class="returnvalue">jsonb</code>517       </p>518       <p>519        Builds a JSON object out of a text array.  The array must have either520        exactly one dimension with an even number of members, in which case521        they are taken as alternating key/value pairs, or two dimensions522        such that each inner array has exactly two elements, which523        are taken as a key/value pair.  All values are converted to JSON524        strings.525       </p>526       <p>527        <code class="literal">json_object('{a, 1, b, "def", c, 3.5}')</code>528        → <code class="returnvalue">{"a" : "1", "b" : "def", "c" : "3.5"}</code>529       </p>530        <p><code class="literal">json_object('{{a, 1}, {b, "def"}, {c, 3.5}}')</code>531        → <code class="returnvalue">{"a" : "1", "b" : "def", "c" : "3.5"}</code>532       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">533        <code class="function">json_object</code> ( <em class="parameter"><code>keys</code></em> <code class="type">text[]</code>, <em class="parameter"><code>values</code></em> <code class="type">text[]</code> )534        → <code class="returnvalue">json</code>535       </p>536       <p class="func_signature">537        <code class="function">jsonb_object</code> ( <em class="parameter"><code>keys</code></em> <code class="type">text[]</code>, <em class="parameter"><code>values</code></em> <code class="type">text[]</code> )538        → <code class="returnvalue">jsonb</code>539       </p>540       <p>541        This form of <code class="function">json_object</code> takes keys and values542        pairwise from separate text arrays.  Otherwise it is identical to543        the one-argument form.544       </p>545       <p>546        <code class="literal">json_object('{a,b}', '{1,2}')</code>547        → <code class="returnvalue">{"a": "1", "b": "2"}</code>548       </p></td></tr></tbody><tbody class="footnotes"><tr><td colspan="1"><div id="ftn.id-1.5.8.22.8.9.2.2.1.1.3.4" class="footnote"><p><a href="#id-1.5.8.22.8.9.2.2.1.1.3.4" class="para"><sup class="para">[a] </sup></a>549          For example, the <a class="xref" href="hstore.html" title="F.18. hstore — hstore key/value datatype">hstore</a> extension has a cast550          from <code class="type">hstore</code> to <code class="type">json</code>, so that551          <code class="type">hstore</code> values converted via the JSON creation functions552          will be represented as JSON objects, not as primitive string values.553         </p></div></td></tr></tbody></table></div></div><br class="table-break" /><p>554   <a class="xref" href="functions-json.html#FUNCTIONS-SQLJSON-MISC" title="Table 9.48. SQL/JSON Testing Functions">Table 9.48</a> details SQL/JSON555   facilities for testing JSON.556  </p><div class="table" id="FUNCTIONS-SQLJSON-MISC"><p class="title"><strong>Table 9.48. SQL/JSON Testing Functions</strong></p><div class="table-contents"><table class="table" summary="SQL/JSON Testing Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">557        Function signature558       </p>559       <p>560        Description561       </p>562       <p>563        Example(s)564      </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">565        <a id="id-1.5.8.22.8.11.2.2.1.1.1.1" class="indexterm"></a>566        <em class="replaceable"><code>expression</code></em> <code class="literal">IS</code> [<span class="optional"> <code class="literal">NOT</code> </span>] <code class="literal">JSON</code>567        [<span class="optional"> { <code class="literal">VALUE</code> | <code class="literal">SCALAR</code> | <code class="literal">ARRAY</code> | <code class="literal">OBJECT</code> } </span>]568        [<span class="optional"> { <code class="literal">WITH</code> | <code class="literal">WITHOUT</code> } <code class="literal">UNIQUE</code> [<span class="optional"> <code class="literal">KEYS</code> </span>] </span>]569       </p>570       <p>571        This predicate tests whether <em class="replaceable"><code>expression</code></em> can be572        parsed as JSON, possibly of a specified type.573        If <code class="literal">SCALAR</code> or <code class="literal">ARRAY</code> or574        <code class="literal">OBJECT</code> is specified, the575        test is whether or not the JSON is of that particular type. If576        <code class="literal">WITH UNIQUE KEYS</code> is specified, then any object in the577        <em class="replaceable"><code>expression</code></em> is also tested to see if it578        has duplicate keys.579       </p>580       <p>581</p><pre class="programlisting">582SELECT js,583  js IS JSON "json?",584  js IS JSON SCALAR "scalar?",585  js IS JSON OBJECT "object?",586  js IS JSON ARRAY "array?"587FROM (VALUES588      ('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js);589     js     | json? | scalar? | object? | array?590------------+-------+---------+---------+--------591 123        | t     | t       | f       | f592 "abc"      | t     | t       | f       | f593 {"a": "b"} | t     | f       | t       | f594 [1,2]      | t     | f       | f       | t595 abc        | f     | f       | f       | f596</pre><p>597       </p>598       <p>599</p><pre class="programlisting">600SELECT js,601  js IS JSON OBJECT "object?",602  js IS JSON ARRAY "array?",603  js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",604  js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"605FROM (VALUES ('[{"a":"1"},606 {"b":"2","b":"3"}]')) foo(js);607-[ RECORD 1 ]-+--------------------608js            | [{"a":"1"},        +609              |  {"b":"2","b":"3"}]610object?       | f611array?        | t612array w. UK?  | f613array w/o UK? | t614</pre><p>615      </p></td></tr></tbody></table></div></div><br class="table-break" /><p>616   <a class="xref" href="functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE" title="Table 9.49. JSON Processing Functions">Table 9.49</a> shows the functions that617   are available for processing <code class="type">json</code> and <code class="type">jsonb</code> values.618  </p><div class="table" id="FUNCTIONS-JSON-PROCESSING-TABLE"><p class="title"><strong>Table 9.49. JSON Processing Functions</strong></p><div class="table-contents"><table class="table" summary="JSON Processing Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">619        Function620       </p>621       <p>622        Description623       </p>624       <p>625        Example(s)626       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">627        <a id="id-1.5.8.22.8.13.2.2.1.1.1.1" class="indexterm"></a>628        <code class="function">json_array_elements</code> ( <code class="type">json</code> )629        → <code class="returnvalue">setof json</code>630       </p>631       <p class="func_signature">632        <a id="id-1.5.8.22.8.13.2.2.1.1.2.1" class="indexterm"></a>633        <code class="function">jsonb_array_elements</code> ( <code class="type">jsonb</code> )634        → <code class="returnvalue">setof jsonb</code>635       </p>636       <p>637        Expands the top-level JSON array into a set of JSON values.638       </p>639       <p>640        <code class="literal">select * from json_array_elements('[1,true, [2,false]]')</code>641        → <code class="returnvalue"></code>642</p><pre class="programlisting">643   value644-----------645 1646 true647 [2,false]648</pre><p>649       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">650        <a id="id-1.5.8.22.8.13.2.2.2.1.1.1" class="indexterm"></a>651        <code class="function">json_array_elements_text</code> ( <code class="type">json</code> )652        → <code class="returnvalue">setof text</code>653       </p>654       <p class="func_signature">655        <a id="id-1.5.8.22.8.13.2.2.2.1.2.1" class="indexterm"></a>656        <code class="function">jsonb_array_elements_text</code> ( <code class="type">jsonb</code> )657        → <code class="returnvalue">setof text</code>658       </p>659       <p>660        Expands the top-level JSON array into a set of <code class="type">text</code> values.661       </p>662       <p>663        <code class="literal">select * from json_array_elements_text('["foo", "bar"]')</code>664        → <code class="returnvalue"></code>665</p><pre class="programlisting">666   value667-----------668 foo669 bar670</pre><p>671       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">672        <a id="id-1.5.8.22.8.13.2.2.3.1.1.1" class="indexterm"></a>673        <code class="function">json_array_length</code> ( <code class="type">json</code> )674        → <code class="returnvalue">integer</code>675       </p>676       <p class="func_signature">677        <a id="id-1.5.8.22.8.13.2.2.3.1.2.1" class="indexterm"></a>678        <code class="function">jsonb_array_length</code> ( <code class="type">jsonb</code> )679        → <code class="returnvalue">integer</code>680       </p>681       <p>682        Returns the number of elements in the top-level JSON array.683       </p>684       <p>685        <code class="literal">json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</code>686        → <code class="returnvalue">5</code>687       </p>688       <p>689        <code class="literal">jsonb_array_length('[]')</code>690        → <code class="returnvalue">0</code>691       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">692        <a id="id-1.5.8.22.8.13.2.2.4.1.1.1" class="indexterm"></a>693        <code class="function">json_each</code> ( <code class="type">json</code> )694        → <code class="returnvalue">setof record</code>695        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,696        <em class="parameter"><code>value</code></em> <code class="type">json</code> )697       </p>698       <p class="func_signature">699        <a id="id-1.5.8.22.8.13.2.2.4.1.2.1" class="indexterm"></a>700        <code class="function">jsonb_each</code> ( <code class="type">jsonb</code> )701        → <code class="returnvalue">setof record</code>702        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,703        <em class="parameter"><code>value</code></em> <code class="type">jsonb</code> )704       </p>705       <p>706        Expands the top-level JSON object into a set of key/value pairs.707       </p>708       <p>709        <code class="literal">select * from json_each('{"a":"foo", "b":"bar"}')</code>710        → <code class="returnvalue"></code>711</p><pre class="programlisting">712 key | value713-----+-------714 a   | "foo"715 b   | "bar"716</pre><p>717       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">718        <a id="id-1.5.8.22.8.13.2.2.5.1.1.1" class="indexterm"></a>719        <code class="function">json_each_text</code> ( <code class="type">json</code> )720        → <code class="returnvalue">setof record</code>721        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,722        <em class="parameter"><code>value</code></em> <code class="type">text</code> )723       </p>724       <p class="func_signature">725        <a id="id-1.5.8.22.8.13.2.2.5.1.2.1" class="indexterm"></a>726        <code class="function">jsonb_each_text</code> ( <code class="type">jsonb</code> )727        → <code class="returnvalue">setof record</code>728        ( <em class="parameter"><code>key</code></em> <code class="type">text</code>,729        <em class="parameter"><code>value</code></em> <code class="type">text</code> )730       </p>731       <p>732        Expands the top-level JSON object into a set of key/value pairs.733        The returned <em class="parameter"><code>value</code></em>s will be of734        type <code class="type">text</code>.735       </p>736       <p>737        <code class="literal">select * from json_each_text('{"a":"foo", "b":"bar"}')</code>738        → <code class="returnvalue"></code>739</p><pre class="programlisting">740 key | value741-----+-------742 a   | foo743 b   | bar744</pre><p>745       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">746        <a id="id-1.5.8.22.8.13.2.2.6.1.1.1" class="indexterm"></a>747        <code class="function">json_extract_path</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">json</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )748        → <code class="returnvalue">json</code>749       </p>750       <p class="func_signature">751        <a id="id-1.5.8.22.8.13.2.2.6.1.2.1" class="indexterm"></a>752        <code class="function">jsonb_extract_path</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )753        → <code class="returnvalue">jsonb</code>754       </p>755       <p>756        Extracts JSON sub-object at the specified path.757        (This is functionally equivalent to the <code class="literal">#&gt;</code>758        operator, but writing the path out as a variadic list can be more759        convenient in some cases.)760       </p>761       <p>762        <code class="literal">json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')</code>763        → <code class="returnvalue">"foo"</code>764       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">765        <a id="id-1.5.8.22.8.13.2.2.7.1.1.1" class="indexterm"></a>766        <code class="function">json_extract_path_text</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">json</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )767        → <code class="returnvalue">text</code>768       </p>769       <p class="func_signature">770        <a id="id-1.5.8.22.8.13.2.2.7.1.2.1" class="indexterm"></a>771        <code class="function">jsonb_extract_path_text</code> ( <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code>, <code class="literal">VARIADIC</code> <em class="parameter"><code>path_elems</code></em> <code class="type">text[]</code> )772        → <code class="returnvalue">text</code>773       </p>774       <p>775        Extracts JSON sub-object at the specified path as <code class="type">text</code>.776        (This is functionally equivalent to the <code class="literal">#&gt;&gt;</code>777        operator.)778       </p>779       <p>780        <code class="literal">json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}', 'f4', 'f6')</code>781        → <code class="returnvalue">foo</code>782       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">783        <a id="id-1.5.8.22.8.13.2.2.8.1.1.1" class="indexterm"></a>784        <code class="function">json_object_keys</code> ( <code class="type">json</code> )785        → <code class="returnvalue">setof text</code>786       </p>787       <p class="func_signature">788        <a id="id-1.5.8.22.8.13.2.2.8.1.2.1" class="indexterm"></a>789        <code class="function">jsonb_object_keys</code> ( <code class="type">jsonb</code> )790        → <code class="returnvalue">setof text</code>791       </p>792       <p>793        Returns the set of keys in the top-level JSON object.794       </p>795       <p>796        <code class="literal">select * from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</code>797        → <code class="returnvalue"></code>798</p><pre class="programlisting">799 json_object_keys800------------------801 f1802 f2803</pre><p>804       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">805        <a id="id-1.5.8.22.8.13.2.2.9.1.1.1" class="indexterm"></a>806        <code class="function">json_populate_record</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">json</code> )807        → <code class="returnvalue">anyelement</code>808       </p>809       <p class="func_signature">810        <a id="id-1.5.8.22.8.13.2.2.9.1.2.1" class="indexterm"></a>811        <code class="function">jsonb_populate_record</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code> )812        → <code class="returnvalue">anyelement</code>813       </p>814       <p>815        Expands the top-level JSON object to a row having the composite type816        of the <em class="parameter"><code>base</code></em> argument.  The JSON object817        is scanned for fields whose names match column names of the output row818        type, and their values are inserted into those columns of the output.819        (Fields that do not correspond to any output column name are ignored.)820        In typical use, the value of <em class="parameter"><code>base</code></em> is just821        <code class="literal">NULL</code>, which means that any output columns that do822        not match any object field will be filled with nulls.  However,823        if <em class="parameter"><code>base</code></em> isn't <code class="literal">NULL</code> then824        the values it contains will be used for unmatched columns.825       </p>826       <p>827        To convert a JSON value to the SQL type of an output column, the828        following rules are applied in sequence:829        </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: disc; "><li class="listitem"><p>830           A JSON null value is converted to an SQL null in all cases.831          </p></li><li class="listitem"><p>832           If the output column is of type <code class="type">json</code>833           or <code class="type">jsonb</code>, the JSON value is just reproduced exactly.834          </p></li><li class="listitem"><p>835           If the output column is a composite (row) type, and the JSON value836           is a JSON object, the fields of the object are converted to columns837           of the output row type by recursive application of these rules.838          </p></li><li class="listitem"><p>839           Likewise, if the output column is an array type and the JSON value840           is a JSON array, the elements of the JSON array are converted to841           elements of the output array by recursive application of these842           rules.843          </p></li><li class="listitem"><p>844           Otherwise, if the JSON value is a string, the contents of the845           string are fed to the input conversion function for the column's846           data type.847          </p></li><li class="listitem"><p>848           Otherwise, the ordinary text representation of the JSON value is849           fed to the input conversion function for the column's data type.850          </p></li></ul></div><p>851       </p>852       <p>853        While the example below uses a constant JSON value, typical use would854        be to reference a <code class="type">json</code> or <code class="type">jsonb</code> column855        laterally from another table in the query's <code class="literal">FROM</code>856        clause.  Writing <code class="function">json_populate_record</code> in857        the <code class="literal">FROM</code> clause is good practice, since all of the858        extracted columns are available for use without duplicate function859        calls.860       </p>861       <p>862        <code class="literal">create type subrowtype as (d int, e text);</code>863        <code class="literal">create type myrowtype as (a int, b text[], c subrowtype);</code>864       </p>865       <p>866        <code class="literal">select * from json_populate_record(null::myrowtype,867         '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a  b c"}, "x": "foo"}')</code>868        → <code class="returnvalue"></code>869</p><pre class="programlisting">870 a |   b       |      c871---+-----------+-------------872 1 | {2,"a b"} | (4,"a b c")873</pre><p>874       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">875        <a id="id-1.5.8.22.8.13.2.2.10.1.1.1" class="indexterm"></a>876        <code class="function">json_populate_recordset</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">json</code> )877        → <code class="returnvalue">setof anyelement</code>878       </p>879       <p class="func_signature">880        <a id="id-1.5.8.22.8.13.2.2.10.1.2.1" class="indexterm"></a>881        <code class="function">jsonb_populate_recordset</code> ( <em class="parameter"><code>base</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>from_json</code></em> <code class="type">jsonb</code> )882        → <code class="returnvalue">setof anyelement</code>883       </p>884       <p>885        Expands the top-level JSON array of objects to a set of rows having886        the composite type of the <em class="parameter"><code>base</code></em> argument.887        Each element of the JSON array is processed as described above888        for <code class="function">json[b]_populate_record</code>.889       </p>890       <p>891        <code class="literal">create type twoints as (a int, b int);</code>892       </p>893       <p>894        <code class="literal">select * from json_populate_recordset(null::twoints, '[{"a":1,"b":2}, {"a":3,"b":4}]')</code>895        → <code class="returnvalue"></code>896</p><pre class="programlisting">897 a | b898---+---899 1 | 2900 3 | 4901</pre><p>902       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">903        <a id="id-1.5.8.22.8.13.2.2.11.1.1.1" class="indexterm"></a>904        <code class="function">json_to_record</code> ( <code class="type">json</code> )905        → <code class="returnvalue">record</code>906       </p>907       <p class="func_signature">908        <a id="id-1.5.8.22.8.13.2.2.11.1.2.1" class="indexterm"></a>909        <code class="function">jsonb_to_record</code> ( <code class="type">jsonb</code> )910        → <code class="returnvalue">record</code>911       </p>912       <p>913        Expands the top-level JSON object to a row having the composite type914        defined by an <code class="literal">AS</code> clause.  (As with all functions915        returning <code class="type">record</code>, the calling query must explicitly916        define the structure of the record with an <code class="literal">AS</code>917        clause.)  The output record is filled from fields of the JSON object,918        in the same way as described above919        for <code class="function">json[b]_populate_record</code>.  Since there is no920        input record value, unmatched columns are always filled with nulls.921       </p>922       <p>923        <code class="literal">create type myrowtype as (a int, b text);</code>924       </p>925       <p>926        <code class="literal">select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)</code>927        → <code class="returnvalue"></code>928</p><pre class="programlisting">929 a |    b    |    c    | d |       r930---+---------+---------+---+---------------931 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")932</pre><p>933       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">934        <a id="id-1.5.8.22.8.13.2.2.12.1.1.1" class="indexterm"></a>935        <code class="function">json_to_recordset</code> ( <code class="type">json</code> )936        → <code class="returnvalue">setof record</code>937       </p>938       <p class="func_signature">939        <a id="id-1.5.8.22.8.13.2.2.12.1.2.1" class="indexterm"></a>940        <code class="function">jsonb_to_recordset</code> ( <code class="type">jsonb</code> )941        → <code class="returnvalue">setof record</code>942       </p>943       <p>944        Expands the top-level JSON array of objects to a set of rows having945        the composite type defined by an <code class="literal">AS</code> clause.  (As946        with all functions returning <code class="type">record</code>, the calling query947        must explicitly define the structure of the record with948        an <code class="literal">AS</code> clause.)  Each element of the JSON array is949        processed as described above950        for <code class="function">json[b]_populate_record</code>.951       </p>952       <p>953        <code class="literal">select * from json_to_recordset('[{"a":1,"b":"foo"}, {"a":"2","c":"bar"}]') as x(a int, b text)</code>954        → <code class="returnvalue"></code>955</p><pre class="programlisting">956 a |  b957---+-----958 1 | foo959 2 |960</pre><p>961       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">962        <a id="id-1.5.8.22.8.13.2.2.13.1.1.1" class="indexterm"></a>963        <code class="function">jsonb_set</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">text[]</code>, <em class="parameter"><code>new_value</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>create_if_missing</code></em> <code class="type">boolean</code> </span>] )964        → <code class="returnvalue">jsonb</code>965       </p>966       <p>967        Returns <em class="parameter"><code>target</code></em>968        with the item designated by <em class="parameter"><code>path</code></em>969        replaced by <em class="parameter"><code>new_value</code></em>, or with970        <em class="parameter"><code>new_value</code></em> added if971        <em class="parameter"><code>create_if_missing</code></em> is true (which is the972        default) and the item designated by <em class="parameter"><code>path</code></em>973        does not exist.974        All earlier steps in the path must exist, or975        the <em class="parameter"><code>target</code></em> is returned unchanged.976        As with the path oriented operators, negative integers that977        appear in the <em class="parameter"><code>path</code></em> count from the end978        of JSON arrays.979        If the last path step is an array index that is out of range,980        and <em class="parameter"><code>create_if_missing</code></em> is true, the new981        value is added at the beginning of the array if the index is negative,982        or at the end of the array if it is positive.983       </p>984       <p>985        <code class="literal">jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', '[2,3,4]', false)</code>986        → <code class="returnvalue">[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]</code>987       </p>988       <p>989        <code class="literal">jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}', '[2,3,4]')</code>990        → <code class="returnvalue">[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</code>991       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">992        <a id="id-1.5.8.22.8.13.2.2.14.1.1.1" class="indexterm"></a>993        <code class="function">jsonb_set_lax</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">text[]</code>, <em class="parameter"><code>new_value</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>create_if_missing</code></em> <code class="type">boolean</code> [<span class="optional">, <em class="parameter"><code>null_value_treatment</code></em> <code class="type">text</code> </span>]</span>] )994        → <code class="returnvalue">jsonb</code>995       </p>996       <p>997        If <em class="parameter"><code>new_value</code></em> is not <code class="literal">NULL</code>,998        behaves identically to <code class="literal">jsonb_set</code>. Otherwise behaves999        according to the value1000        of <em class="parameter"><code>null_value_treatment</code></em> which must be one1001        of <code class="literal">'raise_exception'</code>,1002        <code class="literal">'use_json_null'</code>, <code class="literal">'delete_key'</code>, or1003        <code class="literal">'return_target'</code>. The default is1004        <code class="literal">'use_json_null'</code>.1005       </p>1006       <p>1007        <code class="literal">jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)</code>1008        → <code class="returnvalue">[{"f1": null, "f2": null}, 2, null, 3]</code>1009       </p>1010       <p>1011        <code class="literal">jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')</code>1012        → <code class="returnvalue">[{"f1": 99, "f2": null}, 2]</code>1013       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1014        <a id="id-1.5.8.22.8.13.2.2.15.1.1.1" class="indexterm"></a>1015        <code class="function">jsonb_insert</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">text[]</code>, <em class="parameter"><code>new_value</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>insert_after</code></em> <code class="type">boolean</code> </span>] )1016        → <code class="returnvalue">jsonb</code>1017       </p>1018       <p>1019        Returns <em class="parameter"><code>target</code></em>1020        with <em class="parameter"><code>new_value</code></em> inserted.  If the item1021        designated by the <em class="parameter"><code>path</code></em> is an array1022        element, <em class="parameter"><code>new_value</code></em> will be inserted before1023        that item if <em class="parameter"><code>insert_after</code></em> is false (which1024        is the default), or after it1025        if <em class="parameter"><code>insert_after</code></em> is true.  If the item1026        designated by the <em class="parameter"><code>path</code></em> is an object1027        field, <em class="parameter"><code>new_value</code></em> will be inserted only if1028        the object does not already contain that key.1029        All earlier steps in the path must exist, or1030        the <em class="parameter"><code>target</code></em> is returned unchanged.1031        As with the path oriented operators, negative integers that1032        appear in the <em class="parameter"><code>path</code></em> count from the end1033        of JSON arrays.1034        If the last path step is an array index that is out of range, the new1035        value is added at the beginning of the array if the index is negative,1036        or at the end of the array if it is positive.1037       </p>1038       <p>1039        <code class="literal">jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')</code>1040        → <code class="returnvalue">{"a": [0, "new_value", 1, 2]}</code>1041       </p>1042       <p>1043        <code class="literal">jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)</code>1044        → <code class="returnvalue">{"a": [0, 1, "new_value", 2]}</code>1045       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1046        <a id="id-1.5.8.22.8.13.2.2.16.1.1.1" class="indexterm"></a>1047        <code class="function">json_strip_nulls</code> ( <code class="type">json</code> )1048        → <code class="returnvalue">json</code>1049       </p>1050       <p class="func_signature">1051        <a id="id-1.5.8.22.8.13.2.2.16.1.2.1" class="indexterm"></a>1052        <code class="function">jsonb_strip_nulls</code> ( <code class="type">jsonb</code> )1053        → <code class="returnvalue">jsonb</code>1054       </p>1055       <p>1056        Deletes all object fields that have null values from the given JSON1057        value, recursively.  Null values that are not object fields are1058        untouched.1059       </p>1060       <p>1061        <code class="literal">json_strip_nulls('[{"f1":1, "f2":null}, 2, null, 3]')</code>1062        → <code class="returnvalue">[{"f1":1},2,null,3]</code>1063       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1064        <a id="id-1.5.8.22.8.13.2.2.17.1.1.1" class="indexterm"></a>1065        <code class="function">jsonb_path_exists</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1066        → <code class="returnvalue">boolean</code>1067       </p>1068       <p>1069        Checks whether the JSON path returns any item for the specified JSON1070        value.1071        If the <em class="parameter"><code>vars</code></em> argument is specified, it must1072        be a JSON object, and its fields provide named values to be1073        substituted into the <code class="type">jsonpath</code> expression.1074        If the <em class="parameter"><code>silent</code></em> argument is specified and1075        is <code class="literal">true</code>, the function suppresses the same errors1076        as the <code class="literal">@?</code> and <code class="literal">@@</code> operators do.1077       </p>1078       <p>1079        <code class="literal">jsonb_path_exists('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>1080        → <code class="returnvalue">t</code>1081       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1082        <a id="id-1.5.8.22.8.13.2.2.18.1.1.1" class="indexterm"></a>1083        <code class="function">jsonb_path_match</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1084        → <code class="returnvalue">boolean</code>1085       </p>1086       <p>1087        Returns the result of a JSON path predicate check for the specified1088        JSON value.  Only the first item of the result is taken into account.1089        If the result is not Boolean, then <code class="literal">NULL</code> is returned.1090        The optional <em class="parameter"><code>vars</code></em>1091        and <em class="parameter"><code>silent</code></em> arguments act the same as1092        for <code class="function">jsonb_path_exists</code>.1093       </p>1094       <p>1095        <code class="literal">jsonb_path_match('{"a":[1,2,3,4,5]}', 'exists($.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max))', '{"min":2, "max":4}')</code>1096        → <code class="returnvalue">t</code>1097       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1098        <a id="id-1.5.8.22.8.13.2.2.19.1.1.1" class="indexterm"></a>1099        <code class="function">jsonb_path_query</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1100        → <code class="returnvalue">setof jsonb</code>1101       </p>1102       <p>1103        Returns all JSON items returned by the JSON path for the specified1104        JSON value.1105        The optional <em class="parameter"><code>vars</code></em>1106        and <em class="parameter"><code>silent</code></em> arguments act the same as1107        for <code class="function">jsonb_path_exists</code>.1108       </p>1109       <p>1110        <code class="literal">select * from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>1111        → <code class="returnvalue"></code>1112</p><pre class="programlisting">1113 jsonb_path_query1114------------------1115 21116 31117 41118</pre><p>1119       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1120        <a id="id-1.5.8.22.8.13.2.2.20.1.1.1" class="indexterm"></a>1121        <code class="function">jsonb_path_query_array</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1122        → <code class="returnvalue">jsonb</code>1123       </p>1124       <p>1125        Returns all JSON items returned by the JSON path for the specified1126        JSON value, as a JSON array.1127        The optional <em class="parameter"><code>vars</code></em>1128        and <em class="parameter"><code>silent</code></em> arguments act the same as1129        for <code class="function">jsonb_path_exists</code>.1130       </p>1131       <p>1132        <code class="literal">jsonb_path_query_array('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>1133        → <code class="returnvalue">[2, 3, 4]</code>1134       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1135        <a id="id-1.5.8.22.8.13.2.2.21.1.1.1" class="indexterm"></a>1136        <code class="function">jsonb_path_query_first</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1137        → <code class="returnvalue">jsonb</code>1138       </p>1139       <p>1140        Returns the first JSON item returned by the JSON path for the1141        specified JSON value.  Returns <code class="literal">NULL</code> if there are no1142        results.1143        The optional <em class="parameter"><code>vars</code></em>1144        and <em class="parameter"><code>silent</code></em> arguments act the same as1145        for <code class="function">jsonb_path_exists</code>.1146       </p>1147       <p>1148        <code class="literal">jsonb_path_query_first('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ &gt;= $min &amp;&amp; @ &lt;= $max)', '{"min":2, "max":4}')</code>1149        → <code class="returnvalue">2</code>1150       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1151        <a id="id-1.5.8.22.8.13.2.2.22.1.1.1" class="indexterm"></a>1152        <code class="function">jsonb_path_exists_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1153        → <code class="returnvalue">boolean</code>1154       </p>1155       <p class="func_signature">1156        <a id="id-1.5.8.22.8.13.2.2.22.1.2.1" class="indexterm"></a>1157        <code class="function">jsonb_path_match_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1158        → <code class="returnvalue">boolean</code>1159       </p>1160       <p class="func_signature">1161        <a id="id-1.5.8.22.8.13.2.2.22.1.3.1" class="indexterm"></a>1162        <code class="function">jsonb_path_query_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1163        → <code class="returnvalue">setof jsonb</code>1164       </p>1165       <p class="func_signature">1166        <a id="id-1.5.8.22.8.13.2.2.22.1.4.1" class="indexterm"></a>1167        <code class="function">jsonb_path_query_array_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1168        → <code class="returnvalue">jsonb</code>1169       </p>1170       <p class="func_signature">1171        <a id="id-1.5.8.22.8.13.2.2.22.1.5.1" class="indexterm"></a>1172        <code class="function">jsonb_path_query_first_tz</code> ( <em class="parameter"><code>target</code></em> <code class="type">jsonb</code>, <em class="parameter"><code>path</code></em> <code class="type">jsonpath</code> [<span class="optional">, <em class="parameter"><code>vars</code></em> <code class="type">jsonb</code> [<span class="optional">, <em class="parameter"><code>silent</code></em> <code class="type">boolean</code> </span>]</span>] )1173        → <code class="returnvalue">jsonb</code>1174       </p>1175       <p>1176        These functions act like their counterparts described above without1177        the <code class="literal">_tz</code> suffix, except that these functions support1178        comparisons of date/time values that require timezone-aware1179        conversions.  The example below requires interpretation of the1180        date-only value <code class="literal">2015-08-02</code> as a timestamp with time1181        zone, so the result depends on the current1182        <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting.  Due to this dependency, these1183        functions are marked as stable, which means these functions cannot be1184        used in indexes.  Their counterparts are immutable, and so can be used1185        in indexes; but they will throw errors if asked to make such1186        comparisons.1187       </p>1188       <p>1189        <code class="literal">jsonb_path_exists_tz('["2015-08-01 12:00:00-05"]', '$[*] ? (@.datetime() &lt; "2015-08-02".datetime())')</code>1190        → <code class="returnvalue">t</code>1191       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1192        <a id="id-1.5.8.22.8.13.2.2.23.1.1.1" class="indexterm"></a>1193        <code class="function">jsonb_pretty</code> ( <code class="type">jsonb</code> )1194        → <code class="returnvalue">text</code>1195       </p>1196       <p>1197        Converts the given JSON value to pretty-printed, indented text.1198       </p>1199       <p>1200        <code class="literal">jsonb_pretty('[{"f1":1,"f2":null}, 2]')</code>

Showing the first 1,200 of 1927 lines. Download the file for the rest.

codekingpro/portable-devtools · Team Ai