-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathjson.html
152 lines (131 loc) · 9.61 KB
/
json.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<!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>5.10. JSON Functions — Presto 0.102 Documentation</title>
<link rel="stylesheet" href="../_static/presto.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.102',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<link rel="top" title="Presto 0.102 Documentation" href="../index.html" />
<link rel="up" title="5. Functions and Operators" href="../functions.html" />
<link rel="next" title="5.11. URL Functions" href="url.html" />
<link rel="prev" title="5.9. Regular Expression Functions" href="regexp.html" />
</head>
<body>
<div class="header">
<h1 class="heading"><a href="../index.html">
<span>Presto 0.102 Documentation</span></a></h1>
<h2 class="heading"><span>5.10. JSON Functions</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="regexp.html">5.9. Regular Expression Functions</a>
</span>
<span class="right">
<a href="url.html">5.11. URL Functions</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="json-functions">
<h1>5.10. JSON Functions</h1>
<dl class="function">
<dt id="json_array_contains">
<tt class="descname">json_array_contains</tt><big>(</big><em>json</em>, <em>value</em><big>)</big> → boolean</dt>
<dd><p>Determine if <tt class="docutils literal"><span class="pre">value</span></tt> exists in <tt class="docutils literal"><span class="pre">json</span></tt> (a string containing a JSON array).</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_array_contains</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span><span class="p">,</span> <span class="mi">2</span><span class="p">);</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="json_array_length">
<tt class="descname">json_array_length</tt><big>(</big><em>json</em><big>)</big> → bigint</dt>
<dd><p>Returns the array length of <tt class="docutils literal"><span class="pre">json</span></tt> (a string containing a JSON array).</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_array_length</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span><span class="p">);</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="json_extract">
<tt class="descname">json_extract</tt><big>(</big><em>json</em>, <em>json_path</em><big>)</big> → json</dt>
<dd><p>Evaluates the <a class="reference external" href="http://goessner.net/articles/JsonPath/">JSONPath</a>-like expression <tt class="docutils literal"><span class="pre">json_path</span></tt> on <tt class="docutils literal"><span class="pre">json</span></tt>
(a string containing JSON) and returns the result as a JSON string.</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_extract</span><span class="p">(</span><span class="n">json</span><span class="p">,</span> <span class="s1">'$.store.book'</span><span class="p">);</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="json_extract_scalar">
<tt class="descname">json_extract_scalar</tt><big>(</big><em>json</em>, <em>json_path</em><big>)</big> → varchar</dt>
<dd><p>Like <a class="reference internal" href="#json_extract" title="json_extract"><tt class="xref py py-func docutils literal"><span class="pre">json_extract()</span></tt></a>, but returns the result value as a string (as opposed
to being encoded as JSON). The value referenced by <tt class="docutils literal"><span class="pre">json_path</span></tt> must be a
scalar (boolean, number or string).</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_extract_scalar</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span><span class="p">,</span> <span class="s1">'$[2]'</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="n">json_extract_scalar</span><span class="p">(</span><span class="n">json</span><span class="p">,</span> <span class="s1">'$.store.book[0].author'</span><span class="p">);</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="json_array_get">
<tt class="descname">json_array_get</tt><big>(</big><em>json_array</em>, <em>index</em><big>)</big> → varchar</dt>
<dd><p>Returns the element at the specified index into the <tt class="docutils literal"><span class="pre">json_array</span></tt>. The
index is 0-based. For example:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["a", "b", "c"]'</span><span class="p">,</span> <span class="mi">0</span><span class="p">);</span> <span class="o">=></span> <span class="ss">"a"</span>
<span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["a", "b", "c"]'</span><span class="p">,</span> <span class="mi">1</span><span class="p">);</span> <span class="o">=></span> <span class="ss">"b"</span>
</pre></div>
</div>
<p>This function also supports negative indexes for fetching element indexed
from the end of an array. For example:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["c", "b", "a"]'</span><span class="p">,</span> <span class="o">-</span><span class="mi">1</span><span class="p">);</span> <span class="o">=></span> <span class="ss">"a"</span>
<span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["c", "b", "a"]'</span><span class="p">,</span> <span class="o">-</span><span class="mi">2</span><span class="p">);</span> <span class="o">=></span> <span class="ss">"b"</span>
</pre></div>
</div>
<p>If the element at the specified index doesn’t exist, the function returns
null:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'[]'</span><span class="p">,</span> <span class="mi">0</span><span class="p">);</span> <span class="o">=></span> <span class="k">null</span>
<span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["a", "b", "c"]'</span><span class="p">,</span> <span class="mi">10</span><span class="p">);</span> <span class="o">=></span> <span class="k">null</span>
<span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["c", "b", "a"]'</span><span class="p">,</span> <span class="o">-</span><span class="mi">10</span><span class="p">);</span> <span class="o">=></span> <span class="k">null</span>
</pre></div>
</div>
</dd></dl>
<dl class="function">
<dt id="json_size">
<tt class="descname">json_size</tt><big>(</big><em>json</em>, <em>json_path</em><big>)</big> → bigint</dt>
<dd><p>Like <a class="reference internal" href="#json_extract" title="json_extract"><tt class="xref py py-func docutils literal"><span class="pre">json_extract()</span></tt></a>, but returns the size of the value. The size of
an object value is the number of fields and the size of an array is the
number of elements. The size of a scalar value is zero. For example:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">json_size</span><span class="p">(</span><span class="s1">'{ "x": {"a": 1, "b": 2} }'</span><span class="p">,</span> <span class="s1">'$.x'</span><span class="p">);</span> <span class="o">=></span> <span class="mi">2</span>
<span class="k">SELECT</span> <span class="n">json_size</span><span class="p">(</span><span class="s1">'{ "x": [1, 2, 3] }'</span><span class="p">,</span> <span class="s1">'$.x'</span><span class="p">);</span> <span class="o">=></span> <span class="mi">2</span>
<span class="k">SELECT</span> <span class="n">json_size</span><span class="p">(</span><span class="s1">'{ "x": {"a": 1, "b": 2} }'</span><span class="p">,</span> <span class="s1">'$.x.a'</span><span class="p">);</span> <span class="o">=></span> <span class="mi">0</span>
</pre></div>
</div>
</dd></dl>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="regexp.html">5.9. Regular Expression Functions</a>
</span>
<span class="right">
<a href="url.html">5.11. URL Functions</a> »
</span>
</p>
</div>
<div class="footer">
</div>
</body>
</html>