codekingpro/portable-devtools
115k
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">-></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">-></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 -> 2</code>73 → <code class="returnvalue">{"c":"baz"}</code>74 </p>75 <p>76 <code class="literal">'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -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">-></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">-></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 -> '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">->></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">->></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 ->> 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">->></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">->></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 ->> '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">#></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">#></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 #> '{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">#>></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">#>></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 #>> '{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">@></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 @> '{"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"><@</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 <@ '{"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">?&</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 ?& 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[*] ? (@ > 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[*] > 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">#></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">#>></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[*] ? (@ >= $min && @ <= $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[*] ? (@ >= $min && @ <= $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[*] ? (@ >= $min && @ <= $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[*] ? (@ >= $min && @ <= $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[*] ? (@ >= $min && @ <= $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() < "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>