| <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">'{"a":1, "b":0.8}'</span><span class="p">,</span><span class="w"> </span><span class="s1">'a INT, b DOUBLE'</span><span class="p">);</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">"a"</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="ss">"b"</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="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="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">'{"time":"26/08/2015"}'</span><span class="p">,</span><span class="w"> </span><span class="s1">'time Timestamp'</span><span class="p">,</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">'timestampFormat'</span><span class="p">,</span><span class="w"> </span><span class="s1">'dd/MM/yyyy'</span><span class="p">));</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">"time"</span><span class="p">:</span><span class="ss">"26/08/2015"</span><span class="err">}</span><span class="p">)</span><span class="o">|</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="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">'{"a":"b"}'</span><span class="p">,</span><span class="w"> </span><span class="s1">'$.a'</span><span class="p">);</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">"a"</span><span class="p">:</span><span class="ss">"b"</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="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="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">'[1,2,3,4]'</span><span class="p">);</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="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="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">'[1,2,3,{"f1":1,"f2":[5,6]},4]'</span><span class="p">);</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">"f1"</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span><span class="ss">"f2"</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="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="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">'[1,2'</span><span class="p">);</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="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="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">'{}'</span><span class="p">);</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="o">+</span><span class="c1">--------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="p">[]</span><span class="o">|</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">'{"key": "value"}'</span><span class="p">);</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">"key"</span><span class="p">:</span><span class="w"> </span><span class="ss">"value"</span><span class="err">}</span><span class="p">)</span><span class="o">|</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="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">'{"f1":"abc","f2":{"f3":"a", "f4":"b"}}'</span><span class="p">);</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">"f1"</span><span class="p">:</span><span class="ss">"abc"</span><span class="p">,</span><span class="ss">"f2"</span><span class="p">:</span><span class="err">{</span><span class="ss">"f3"</span><span class="p">:</span><span class="ss">"a"</span><span class="p">,</span><span class="w"> </span><span class="ss">"f4"</span><span class="p">:</span><span class="ss">"b"</span><span class="err">}}</span><span class="p">)</span><span class="o">|</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="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">'{"a":1, "b":2}'</span><span class="p">,</span><span class="w"> </span><span class="s1">'a'</span><span class="p">,</span><span class="w"> </span><span class="s1">'b'</span><span class="p">);</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="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="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">'[{"col":0}]'</span><span class="p">);</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">"col"</span><span class="p">:</span><span class="mi">0</span><span class="err">}</span><span class="p">])</span><span class="o">|</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"><</span><span class="n">STRUCT</span><span class="o"><`</span><span class="n">col</span><span class="p">...</span><span class="o">|</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">'[{"col":01}]'</span><span class="p">,</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">'allowNumericLeadingZeros'</span><span class="p">,</span><span class="w"> </span><span class="s1">'true'</span><span class="p">));</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">"col"</span><span class="p">:</span><span class="mi">01</span><span class="err">}</span><span class="p">])</span><span class="o">|</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"><</span><span class="n">STRUCT</span><span class="o"><`</span><span class="n">col</span><span class="p">...</span><span class="o">|</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">'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="s1">'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="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="o">+</span><span class="c1">---------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="ss">"a"</span><span class="p">:</span><span class="mi">1</span><span class="p">,</span><span class="ss">"b"</span><span class="p">:</span><span class="mi">2</span><span class="err">}</span><span class="o">|</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">'time'</span><span class="p">,</span><span class="w"> </span><span class="n">to_timestamp</span><span class="p">(</span><span class="s1">'2015-08-26'</span><span class="p">,</span><span class="w"> </span><span class="s1">'yyyy-MM-dd'</span><span class="p">)),</span><span class="w"> </span><span class="k">map</span><span class="p">(</span><span class="s1">'timestampFormat'</span><span class="p">,</span><span class="w"> </span><span class="s1">'dd/MM/yyyy'</span><span class="p">));</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="o">+</span><span class="c1">-----------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="err">{</span><span class="ss">"time"</span><span class="p">:</span><span class="ss">"26/08/20...|</span> |
| <span class="ss">+-----------------------------------------------------------------+</span> |
| |
| <span class="ss">SELECT to_json(array(named_struct('a', 1, 'b', 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">| [{"</span><span class="n">a</span><span class="ss">":1,"</span><span class="n">b</span><span class="ss">":2}]|</span> |
| <span class="ss">+----------------------------------------+</span> |
| |
| <span class="ss">SELECT to_json(map('a', named_struct('b', 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">| {"</span><span class="n">a</span><span class="ss">":{"</span><span class="n">b</span><span class="ss">":1}}|</span> |
| <span class="ss">+-----------------------------------+</span> |
| |
| <span class="ss">SELECT to_json(map(named_struct('a', 1),named_struct('b', 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">| {"</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span><span class="ss">":{"</span><span class="n">b</span><span class="ss">":2}}|</span> |
| <span class="ss">+----------------------------------------------------+</span> |
| |
| <span class="ss">SELECT to_json(map('a', 1));</span> |
| <span class="ss">+------------------+</span> |
| <span class="ss">|to_json(map(a, 1))|</span> |
| <span class="ss">+------------------+</span> |
| <span class="ss">| {"</span><span class="n">a</span><span class="ss">":1}|</span> |
| <span class="ss">+------------------+</span> |
| |
| <span class="ss">SELECT to_json(array((map('a', 1))));</span> |
| <span class="ss">+-------------------------+</span> |
| <span class="ss">|to_json(array(map(a, 1)))|</span> |
| <span class="ss">+-------------------------+</span> |
| <span class="ss">| [{"</span><span class="n">a</span><span class="err">"</span><span class="p">:</span><span class="mi">1</span><span class="err">}</span><span class="p">]</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">-------------------------+</span> |
| </code></pre></div> |