Google Git
Sign in
apache / spark-website / 2c562896505588421cce257d111c8ba3805528c2 / . / site / docs / 3.3.0 / generated-json-funcs-examples.html
blob: bbce4eba9b67b8e5180d9899bf71e2e0f030536e [file] [log] [blame]
<div class="codehilite"><pre><span></span><code><span class="c1">-- from_json</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">from_json</span><span class="p">(</span><span class="s1">&#39;{&quot;a&quot;:1, &quot;b&quot;:0.8}&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;a INT, b DOUBLE&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------+</span>
<span class="o">|</span><span class="n">from_json</span><span class="p">(</span><span class="err">{</span><span class="ss">&quot;a&quot;</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="ss">&quot;b&quot;</span><span class="p">:</span><span class="mi">0</span><span class="p">.</span><span class="mi">8</span><span class="err">}</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">8</span><span class="err">}</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">from_json</span><span class="p">(</span><span class="s1">&#39;{&quot;time&quot;:&quot;26/08/2015&quot;}&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;time Timestamp&#39;</span><span class="p">,</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">&#39;timestampFormat&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;dd/MM/yyyy&#39;</span><span class="p">));</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------+</span>
<span class="o">|</span><span class="n">from_json</span><span class="p">(</span><span class="err">{</span><span class="ss">&quot;time&quot;</span><span class="p">:</span><span class="ss">&quot;26/08/2015&quot;</span><span class="err">}</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="mi">2015</span><span class="o">-</span><span class="mi">08</span><span class="o">-</span><span class="mi">26</span><span class="w"> </span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="p">...</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">from_json</span><span class="p">(</span><span class="s1">&#39;{&quot;teacher&quot;: &quot;Alice&quot;, &quot;student&quot;: [{&quot;name&quot;: &quot;Bob&quot;, &quot;rank&quot;: 1}, {&quot;name&quot;: &quot;Charlie&quot;, &quot;rank&quot;: 2}]}&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;STRUCT&lt;teacher: STRING, student: ARRAY&lt;STRUCT&lt;name: STRING, rank: INT&gt;&gt;&gt;&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------------------------------------------------------------------------------+</span>
<span class="o">|</span><span class="n">from_json</span><span class="p">(</span><span class="err">{</span><span class="ss">&quot;teacher&quot;</span><span class="p">:</span><span class="w"> </span><span class="ss">&quot;Alice&quot;</span><span class="p">,</span><span class="w"> </span><span class="ss">&quot;student&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">[</span><span class="err">{</span><span class="ss">&quot;name&quot;</span><span class="p">:</span><span class="w"> </span><span class="ss">&quot;Bob&quot;</span><span class="p">,</span><span class="w"> </span><span class="ss">&quot;rank&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="err">}</span><span class="p">,</span><span class="w"> </span><span class="err">{</span><span class="ss">&quot;name&quot;</span><span class="p">:</span><span class="w"> </span><span class="ss">&quot;Charlie&quot;</span><span class="p">,</span><span class="w"> </span><span class="ss">&quot;rank&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">2</span><span class="err">}</span><span class="p">]</span><span class="err">}</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------------------------------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="n">Alice</span><span class="p">,</span><span class="w"> </span><span class="p">[</span><span class="err">{</span><span class="n">Bob</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="err">}</span><span class="p">...</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------------------------------------------------------------------------------+</span>
<span class="c1">-- get_json_object</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">get_json_object</span><span class="p">(</span><span class="s1">&#39;{&quot;a&quot;:&quot;b&quot;}&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;$.a&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="o">|</span><span class="n">get_json_object</span><span class="p">(</span><span class="err">{</span><span class="ss">&quot;a&quot;</span><span class="p">:</span><span class="ss">&quot;b&quot;</span><span class="err">}</span><span class="p">,</span><span class="w"> </span><span class="err">$</span><span class="p">.</span><span class="n">a</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">-------------------------------+</span>
<span class="c1">-- json_array_length</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_array_length</span><span class="p">(</span><span class="s1">&#39;[1,2,3,4]&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="o">|</span><span class="n">json_array_length</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">])</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">4</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_array_length</span><span class="p">(</span><span class="s1">&#39;[1,2,3,{&quot;f1&quot;:1,&quot;f2&quot;:[5,6]},4]&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">------------------------------------------------+</span>
<span class="o">|</span><span class="n">json_array_length</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">,</span><span class="err">{</span><span class="ss">&quot;f1&quot;</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span><span class="ss">&quot;f2&quot;</span><span class="p">:[</span><span class="mi">5</span><span class="p">,</span><span class="mi">6</span><span class="p">]</span><span class="err">}</span><span class="p">,</span><span class="mi">4</span><span class="p">])</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">5</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">------------------------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_array_length</span><span class="p">(</span><span class="s1">&#39;[1,2&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="n">json_array_length</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="k">null</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="c1">-- json_object_keys</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_object_keys</span><span class="p">(</span><span class="s1">&#39;{}&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------+</span>
<span class="o">|</span><span class="n">json_object_keys</span><span class="p">(</span><span class="err">{}</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[]</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_object_keys</span><span class="p">(</span><span class="s1">&#39;{&quot;key&quot;: &quot;value&quot;}&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------------+</span>
<span class="o">|</span><span class="n">json_object_keys</span><span class="p">(</span><span class="err">{</span><span class="ss">&quot;key&quot;</span><span class="p">:</span><span class="w"> </span><span class="ss">&quot;value&quot;</span><span class="err">}</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="k">key</span><span class="p">]</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_object_keys</span><span class="p">(</span><span class="s1">&#39;{&quot;f1&quot;:&quot;abc&quot;,&quot;f2&quot;:{&quot;f3&quot;:&quot;a&quot;, &quot;f4&quot;:&quot;b&quot;}}&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------------------------------+</span>
<span class="o">|</span><span class="n">json_object_keys</span><span class="p">(</span><span class="err">{</span><span class="ss">&quot;f1&quot;</span><span class="p">:</span><span class="ss">&quot;abc&quot;</span><span class="p">,</span><span class="ss">&quot;f2&quot;</span><span class="p">:</span><span class="err">{</span><span class="ss">&quot;f3&quot;</span><span class="p">:</span><span class="ss">&quot;a&quot;</span><span class="p">,</span><span class="w"> </span><span class="ss">&quot;f4&quot;</span><span class="p">:</span><span class="ss">&quot;b&quot;</span><span class="err">}}</span><span class="p">)</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="n">f1</span><span class="p">,</span><span class="w"> </span><span class="n">f2</span><span class="p">]</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">--------------------------------------------------------+</span>
<span class="c1">-- json_tuple</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">json_tuple</span><span class="p">(</span><span class="s1">&#39;{&quot;a&quot;:1, &quot;b&quot;:2}&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;a&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;b&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">---+---+</span>
<span class="o">|</span><span class="w"> </span><span class="n">c0</span><span class="o">|</span><span class="w"> </span><span class="n">c1</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---+---+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---+---+</span>
<span class="c1">-- schema_of_json</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">schema_of_json</span><span class="p">(</span><span class="s1">&#39;[{&quot;col&quot;:0}]&#39;</span><span class="p">);</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------+</span>
<span class="o">|</span><span class="n">schema_of_json</span><span class="p">([</span><span class="err">{</span><span class="ss">&quot;col&quot;</span><span class="p">:</span><span class="mi">0</span><span class="err">}</span><span class="p">])</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="nb">ARRAY</span><span class="o">&lt;</span><span class="n">STRUCT</span><span class="o">&lt;</span><span class="n">col</span><span class="p">:...</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">schema_of_json</span><span class="p">(</span><span class="s1">&#39;[{&quot;col&quot;:01}]&#39;</span><span class="p">,</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">&#39;allowNumericLeadingZeros&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;true&#39;</span><span class="p">));</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="o">|</span><span class="n">schema_of_json</span><span class="p">([</span><span class="err">{</span><span class="ss">&quot;col&quot;</span><span class="p">:</span><span class="mi">01</span><span class="err">}</span><span class="p">])</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="nb">ARRAY</span><span class="o">&lt;</span><span class="n">STRUCT</span><span class="o">&lt;</span><span class="n">col</span><span class="p">:...</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="c1">-- to_json</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">to_json</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="s1">&#39;a&#39;</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;b&#39;</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">));</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------------+</span>
<span class="o">|</span><span class="n">to_json</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">))</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="ss">&quot;a&quot;</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span><span class="ss">&quot;b&quot;</span><span class="p">:</span><span class="mi">2</span><span class="err">}</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">---------------------------------+</span>
<span class="k">SELECT</span><span class="w"> </span><span class="n">to_json</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="s1">&#39;time&#39;</span><span class="p">,</span><span class="w"> </span><span class="n">to_timestamp</span><span class="p">(</span><span class="s1">&#39;2015-08-26&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;yyyy-MM-dd&#39;</span><span class="p">)),</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">&#39;timestampFormat&#39;</span><span class="p">,</span><span class="w"> </span><span class="s1">&#39;dd/MM/yyyy&#39;</span><span class="p">));</span><span class="w"></span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------+</span>
<span class="o">|</span><span class="n">to_json</span><span class="p">(</span><span class="n">named_struct</span><span class="p">(</span><span class="k">time</span><span class="p">,</span><span class="w"> </span><span class="n">to_timestamp</span><span class="p">(</span><span class="mi">2015</span><span class="o">-</span><span class="mi">08</span><span class="o">-</span><span class="mi">26</span><span class="p">,</span><span class="w"> </span><span class="n">yyyy</span><span class="o">-</span><span class="n">MM</span><span class="o">-</span><span class="n">dd</span><span class="p">)))</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------------+</span>
<span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="ss">&quot;time&quot;</span><span class="p">:</span><span class="ss">&quot;26/08/20...|</span>
<span class="ss">+-----------------------------------------------------------------+</span>
<span class="ss">SELECT to_json(array(named_struct(&#39;a&#39;, 1, &#39;b&#39;, 2)));</span>
<span class="ss">+----------------------------------------+</span>
<span class="ss">|to_json(array(named_struct(a, 1, b, 2)))|</span>
<span class="ss">+----------------------------------------+</span>
<span class="ss">| [{&quot;</span><span class="n">a</span><span class="ss">&quot;:1,&quot;</span><span class="n">b</span><span class="ss">&quot;:2}]|</span>
<span class="ss">+----------------------------------------+</span>
<span class="ss">SELECT to_json(map(&#39;a&#39;, named_struct(&#39;b&#39;, 1)));</span>
<span class="ss">+-----------------------------------+</span>
<span class="ss">|to_json(map(a, named_struct(b, 1)))|</span>
<span class="ss">+-----------------------------------+</span>
<span class="ss">| {&quot;</span><span class="n">a</span><span class="ss">&quot;:{&quot;</span><span class="n">b</span><span class="ss">&quot;:1}}|</span>
<span class="ss">+-----------------------------------+</span>
<span class="ss">SELECT to_json(map(named_struct(&#39;a&#39;, 1),named_struct(&#39;b&#39;, 2)));</span>
<span class="ss">+----------------------------------------------------+</span>
<span class="ss">|to_json(map(named_struct(a, 1), named_struct(b, 2)))|</span>
<span class="ss">+----------------------------------------------------+</span>
<span class="ss">| {&quot;</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span><span class="ss">&quot;:{&quot;</span><span class="n">b</span><span class="ss">&quot;:2}}|</span>
<span class="ss">+----------------------------------------------------+</span>
<span class="ss">SELECT to_json(map(&#39;a&#39;, 1));</span>
<span class="ss">+------------------+</span>
<span class="ss">|to_json(map(a, 1))|</span>
<span class="ss">+------------------+</span>
<span class="ss">| {&quot;</span><span class="n">a</span><span class="ss">&quot;:1}|</span>
<span class="ss">+------------------+</span>
<span class="ss">SELECT to_json(array((map(&#39;a&#39;, 1))));</span>
<span class="ss">+-------------------------+</span>
<span class="ss">|to_json(array(map(a, 1)))|</span>
<span class="ss">+-------------------------+</span>
<span class="ss">| [{&quot;</span><span class="n">a</span><span class="err">&quot;</span><span class="p">:</span><span class="mi">1</span><span class="err">}</span><span class="p">]</span><span class="o">|</span><span class="w"></span>
<span class="o">+</span><span class="c1">-------------------------+</span>
</code></pre></div>