376/functions/json.html


<!DOCTYPE html>

<html>
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  <meta name="lang:clipboard.copy" content="Copy to clipboard">
  <meta name="lang:clipboard.copied" content="Copied to clipboard">
  <meta name="lang:search.language" content="en">
  <meta name="lang:search.pipeline.stopwords" content="True">
  <meta name="lang:search.pipeline.trimmer" content="True">
  <meta name="lang:search.result.none" content="No matching documents">
  <meta name="lang:search.result.one" content="1 matching document">
  <meta name="lang:search.result.other" content="# matching documents">
  <meta name="lang:search.tokenizer" content="[\s\-]+">

  
    <link href="https://fonts.gstatic.com/" rel="preconnect" crossorigin>
    <link href="https://fonts.googleapis.com/css?family=Roboto+Mono:400,500,700|Roboto:300,400,400i,700&display=fallback" rel="stylesheet">

    <link href="https://fonts.googleapis.com/css2?family=Alata&family=Open+Sans:ital,wght%400,300;0,400;0,600;1,400&display=swap" rel="stylesheet">
    <style>
      body,
      input {
        font-family: "Roboto", "Helvetica Neue", Helvetica, Arial, sans-serif
      }

      code,
      kbd,
      pre {
        font-family: "Roboto Mono", "Courier New", Courier, monospace
      }
    </style>
  

  <link rel="stylesheet" href="../_static/stylesheets/application.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-palette.css"/>
  <link rel="stylesheet" href="../_static/stylesheets/application-fixes.css"/>
  
  <link rel="stylesheet" href="../_static/fonts/material-icons.css"/>
  
  <meta name="theme-color" content="2196f3">
  <script src="../_static/javascripts/modernizr.js"></script>
  
  
    <title>JSON functions and operators &#8212; Trino 376 Documentation</title>
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <link rel="stylesheet" href="../_static/material.css" type="text/css" />
    <link rel="stylesheet" type="text/css" href="../_static/copybutton.css" />
    <link rel="stylesheet" type="text/css" href="../_static/trino.css" />
    <script id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
    <script src="../_static/jquery.js"></script>
    <script src="../_static/underscore.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/language_data.js"></script>
    <script src="../_static/clipboard.min.js"></script>
    <script src="../_static/copybutton.js"></script>
    <script async="async" src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.7/latest.js?config=TeX-AMS-MML_HTMLorMML"></script>
    <script type="text/x-mathjax-config">MathJax.Hub.Config({"tex2jax": {"inlineMath": [["\\(", "\\)"]], "displayMath": [["\\[", "\\]"]], "processRefs": false, "processEnvironments": false}})</script>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="Lambda expressions" href="lambda.html" />
    <link rel="prev" title="IP Address Functions" href="ipaddress.html" />
  
   
    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-133457846-1"></script>
    <script>
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());
      gtag('config', 'UA-133457846-1');
      gtag('config', 'AW-1036784065');
      window._linkedin_data_partner_ids = ['2842796'];
    </script>
    <script async src="https://snap.licdn.com/li.lms-analytics/insight.min.js"></script>
  </head>
  <body dir=ltr
        data-md-color-primary= data-md-color-accent=>
  
  <svg class="md-svg">
    <defs data-children-count="0">
      
      <svg xmlns="http://www.w3.org/2000/svg" width="416" height="448" viewBox="0 0 416 448" id="__github"><path fill="currentColor" d="M160 304q0 10-3.125 20.5t-10.75 19T128 352t-18.125-8.5-10.75-19T96 304t3.125-20.5 10.75-19T128 256t18.125 8.5 10.75 19T160 304zm160 0q0 10-3.125 20.5t-10.75 19T288 352t-18.125-8.5-10.75-19T256 304t3.125-20.5 10.75-19T288 256t18.125 8.5 10.75 19T320 304zm40 0q0-30-17.25-51T296 232q-10.25 0-48.75 5.25Q229.5 240 208 240t-39.25-2.75Q130.75 232 120 232q-29.5 0-46.75 21T56 304q0 22 8 38.375t20.25 25.75 30.5 15 35 7.375 37.25 1.75h42q20.5 0 37.25-1.75t35-7.375 30.5-15 20.25-25.75T360 304zm56-44q0 51.75-15.25 82.75-9.5 19.25-26.375 33.25t-35.25 21.5-42.5 11.875-42.875 5.5T212 416q-19.5 0-35.5-.75t-36.875-3.125-38.125-7.5-34.25-12.875T37 371.5t-21.5-28.75Q0 312 0 260q0-59.25 34-99-6.75-20.5-6.75-42.5 0-29 12.75-54.5 27 0 47.5 9.875t47.25 30.875Q171.5 96 212 96q37 0 70 8 26.25-20.5 46.75-30.25T376 64q12.75 25.5 12.75 54.5 0 21.75-6.75 42 34 40 34 99.5z"/></svg>
      
    </defs>
  </svg>
  
  <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer">
  <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search">
  <label class="md-overlay" data-md-component="overlay" for="__drawer"></label>
  <a href="#functions/json" tabindex="1" class="md-skip"> Skip to content </a>
  <div id="announcement">
  <div id="announcement-content">
    Presto SQL is now Trino
    <a href="https://trino.io/blog/2020/12/27/announcing-trino.html" target="_blank">Read why &raquo;</a>
  </div>
</div>
<header class="md-header" data-md-component="header">
  <nav class="md-header-nav md-grid">
    <div class="md-flex navheader">
      <div class="md-flex__cell md-flex__cell--shrink">
        <a href="/" title="Trino"
           class="md-header-nav__button md-logo">
          
              <img src="../_static/trino.svg" height="26"
                   alt="Trino 376 Documentation logo">
          
        </a>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--menu md-header-nav__button" for="__drawer"></label>
      </div>
      <div class="md-flex__cell md-flex__cell--stretch">
        <div class="md-flex__ellipsis md-header-nav__title" data-md-component="title">
          <span class="md-header-nav__topic">Trino 376 Documentation</span>
          <span class="md-header-nav__topic"> JSON functions and operators </span>
        </div>
      </div>
      <div class="md-flex__cell md-flex__cell--shrink">
        <label class="md-icon md-icon--search md-header-nav__button" for="__search"></label>
        
<div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" action="../search.html" method="GET" name="search">
      <input type="text" class="md-search__input" name="q" placeholder="Search"
             autocapitalize="off" autocomplete="off" spellcheck="false"
             data-md-component="query" data-md-state="active">
      <label class="md-icon md-search__icon" for="__search"></label>
      <button type="reset" class="md-icon md-search__icon" data-md-component="reset" tabindex="-1">
        &#xE5CD;
      </button>
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="result">
          <div class="md-search-result__meta">
            Type to start searching
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>

      </div>
      
        <div class="md-flex__cell md-flex__cell--shrink">
          <div class="md-header-nav__source">
            <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
          </div>
        </div>
      
      
  <script src="../_static/javascripts/version_dropdown.js"></script>
  <script>
    var json_loc = "../../versions.json",
        target_loc = "../../",
        text = "Versions";
    $( document ).ready( add_version_dropdown(json_loc, target_loc, text));
  </script>
  

    </div>
  </nav>
</header>

  
  <div class="md-container">
    
    
    <!-- empty -->
    <main class="md-main">
      <div class="md-main__inner md-grid" data-md-component="container">
        
          <div class="md-sidebar md-sidebar--primary" data-md-component="navigation">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                <nav class="md-nav md-nav--primary" data-md-level="0">
  <label class="md-nav__title md-nav__title--site" for="__drawer">
    <a href="/" title="Trino" class="md-nav__button md-logo">
      
        <img src="../_static/trino.svg" alt=" logo" width="48" height="48">
      
    </a>
    <a href="../index.html"
       title="Trino 376 Documentation">Trino 376 Documentation</a>
  </label>
    <div class="md-nav__source">
      <a href="https://github.com/trinodb/trino" title="Go to repository" class="md-source" data-md-source="github">

    <div class="md-source__icon">
      <svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 24 24" width="28" height="28">
        <use xlink:href="#__github" width="24" height="24"></use>
      </svg>
    </div>
  
  <div class="md-source__repository">
    Trino
  </div>
</a>
    </div>
  
  
  <ul class="md-nav__list">
    <li class="md-nav__item">
    
    
      <a href="../overview.html" class="md-nav__link">Overview</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../installation.html" class="md-nav__link">Installation</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../client.html" class="md-nav__link">Clients</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../security.html" class="md-nav__link">Security</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html" class="md-nav__link">Administration</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../admin.html#event-listeners" class="md-nav__link">Event listeners</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../optimizer.html" class="md-nav__link">Query optimizer</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../connector.html" class="md-nav__link">Connectors</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../functions.html" class="md-nav__link">Functions and operators</a>
      <ul class="md-nav__list"> 
    <li class="md-nav__item">
    
    
      <a href="aggregate.html" class="md-nav__link">Aggregate</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="array.html" class="md-nav__link">Array</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="binary.html" class="md-nav__link">Binary</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="bitwise.html" class="md-nav__link">Bitwise</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="color.html" class="md-nav__link">Color</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="comparison.html" class="md-nav__link">Comparison</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="conditional.html" class="md-nav__link">Conditional</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="conversion.html" class="md-nav__link">Conversion</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="datetime.html" class="md-nav__link">Date and time</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="decimal.html" class="md-nav__link">Decimal</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="geospatial.html" class="md-nav__link">Geospatial</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="hyperloglog.html" class="md-nav__link">HyperLogLog</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ipaddress.html" class="md-nav__link">IP Address</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
    <input class="md-toggle md-nav__toggle" data-md-toggle="toc" type="checkbox" id="__toc">
    <label class="md-nav__link md-nav__link--active" for="__toc"> JSON </label>
    
      <a href="#" class="md-nav__link md-nav__link--active">JSON</a>
      
        
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#cast-to-json" class="md-nav__link">Cast to JSON</a>
        </li>
        <li class="md-nav__item"><a href="#cast-from-json" class="md-nav__link">Cast from JSON</a>
        </li>
        <li class="md-nav__item"><a href="#json-functions" class="md-nav__link">JSON functions</a>
        </li>
    </ul>
</nav>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="lambda.html" class="md-nav__link">Lambda</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="logical.html" class="md-nav__link">Logical</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="ml.html" class="md-nav__link">Machine learning</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="map.html" class="md-nav__link">Map</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="math.html" class="md-nav__link">Math</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="qdigest.html" class="md-nav__link">Quantile digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="regexp.html" class="md-nav__link">Regular expression</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="session.html" class="md-nav__link">Session</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="setdigest.html" class="md-nav__link">Set Digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="string.html" class="md-nav__link">String</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="system.html" class="md-nav__link">System</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="teradata.html" class="md-nav__link">Teradata</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="tdigest.html" class="md-nav__link">T-Digest</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="url.html" class="md-nav__link">URL</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="uuid.html" class="md-nav__link">UUID</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="window.html" class="md-nav__link">Window</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="list.html" class="md-nav__link">List of functions and operators</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="list-by-topic.html" class="md-nav__link">List of functions by topic</a>
      
    
    </li></ul>
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../language.html" class="md-nav__link">SQL language</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../sql.html" class="md-nav__link">SQL statement syntax</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../develop.html" class="md-nav__link">Developer guide</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../appendix.html" class="md-nav__link">Appendix</a>
      
    
    </li>
    <li class="md-nav__item">
    
    
      <a href="../release.html" class="md-nav__link">Release notes</a>
      
    
    </li>
  </ul>
  

</nav>
              </div>
            </div>
          </div>
          <div class="md-sidebar md-sidebar--secondary" data-md-component="toc">
            <div class="md-sidebar__scrollwrap">
              <div class="md-sidebar__inner">
                
<nav class="md-nav md-nav--secondary">
    <label class="md-nav__title" for="__toc">Contents</label>
    <ul class="md-nav__list" data-md-scrollfix="">
        <li class="md-nav__item"><a href="#cast-to-json" class="md-nav__link">Cast to JSON</a>
        </li>
        <li class="md-nav__item"><a href="#cast-from-json" class="md-nav__link">Cast from JSON</a>
        </li>
        <li class="md-nav__item"><a href="#json-functions" class="md-nav__link">JSON functions</a>
        </li>
    </ul>
</nav>
              </div>
            </div>
          </div>
        
        <div class="md-content">
          <article class="md-content__inner md-typeset" role="main">
            
  
<h1 id="functions-json--page-root">JSON functions and operators<a class="headerlink" href="#functions-json--page-root" title="Permalink to this headline">#</a></h1>

<h2 id="cast-to-json">Cast to JSON<a class="headerlink" href="#cast-to-json" title="Permalink to this headline">#</a></h2>
<p>The following types can be cast to JSON:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">TINYINT</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">INTEGER</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">BIGINT</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">REAL</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">DOUBLE</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code></p></li>
</ul>
<p>Additionally, <code class="docutils literal notranslate"><span class="pre">ARRAY</span></code>, <code class="docutils literal notranslate"><span class="pre">MAP</span></code>, and <code class="docutils literal notranslate"><span class="pre">ROW</span></code> types can be cast to JSON when
the following requirements are met:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">ARRAY</span></code> types can be cast when the element type of the array is one
of the supported types.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">MAP</span></code> types can be cast when the key type of the map is <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code> and
the value type of the map is a supported type,</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">ROW</span></code> types can be cast when every field type of the row is a supported
type.</p></li>
</ul>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Cast operations with supported <a class="reference internal" href="../language/types.html#string-data-types"><span class="std std-ref">character string types</span></a> treat the input as a string, not validated as JSON.
This means that a cast operation with a string-type input of invalid JSON
results in a succesful cast to invalid JSON.</p>
<p>Instead, consider using the <a class="reference internal" href="#json_parse" title="json_parse"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_parse()</span></code></a> function to
create validated JSON from a string.</p>
</div>
<p>The following examples show the behavior of casting to JSON with these types:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="k">NULL</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- NULL</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="mi">1</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '1'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="mi">9223372036854775807</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '9223372036854775807'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'abc'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '"abc"'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="k">true</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON 'true'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="mi">1</span><span class="p">.</span><span class="mi">234</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '1.234'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">23</span><span class="p">,</span> <span class="mi">456</span><span class="p">]</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '[1,23,456]'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="mi">456</span><span class="p">]</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '[1,null,456]'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">23</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">456</span><span class="p">]]</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '[[1,23],[456]]'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="k">MAP</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'k1'</span><span class="p">,</span> <span class="s1">'k2'</span><span class="p">,</span> <span class="s1">'k3'</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">23</span><span class="p">,</span> <span class="mi">456</span><span class="p">])</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '{"k1":1,"k2":23,"k3":456}'</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="k">CAST</span><span class="p">(</span><span class="k">ROW</span><span class="p">(</span><span class="mi">123</span><span class="p">,</span> <span class="s1">'abc'</span><span class="p">,</span> <span class="k">true</span><span class="p">)</span> <span class="k">AS</span>
            <span class="k">ROW</span><span class="p">(</span><span class="n">v1</span> <span class="nb">BIGINT</span><span class="p">,</span> <span class="n">v2</span> <span class="nb">VARCHAR</span><span class="p">,</span> <span class="n">v3</span> <span class="nb">BOOLEAN</span><span class="p">))</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>
<span class="c1">-- JSON '{"v1":123,"v2":"abc","v3":true}'</span>
</pre></div>
</div>
<p>Casting from NULL to <code class="docutils literal notranslate"><span class="pre">JSON</span></code> is not straightforward. Casting
from a standalone <code class="docutils literal notranslate"><span class="pre">NULL</span></code> will produce a SQL <code class="docutils literal notranslate"><span class="pre">NULL</span></code> instead of
<code class="docutils literal notranslate"><span class="pre">JSON</span> <span class="pre">'null'</span></code>. However, when casting from arrays or map containing
<code class="docutils literal notranslate"><span class="pre">NULL</span></code>s, the produced <code class="docutils literal notranslate"><span class="pre">JSON</span></code> will have <code class="docutils literal notranslate"><span class="pre">null</span></code>s in it.</p>


<h2 id="cast-from-json">Cast from JSON<a class="headerlink" href="#cast-from-json" title="Permalink to this headline">#</a></h2>
<p>Casting to <code class="docutils literal notranslate"><span class="pre">BOOLEAN</span></code>, <code class="docutils literal notranslate"><span class="pre">TINYINT</span></code>, <code class="docutils literal notranslate"><span class="pre">SMALLINT</span></code>, <code class="docutils literal notranslate"><span class="pre">INTEGER</span></code>,
<code class="docutils literal notranslate"><span class="pre">BIGINT</span></code>, <code class="docutils literal notranslate"><span class="pre">REAL</span></code>, <code class="docutils literal notranslate"><span class="pre">DOUBLE</span></code> or <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code> is supported.
Casting to <code class="docutils literal notranslate"><span class="pre">ARRAY</span></code> and <code class="docutils literal notranslate"><span class="pre">MAP</span></code> is supported when the element type of
the array is one of the supported types, or when the key type of the map
is <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code> and value type of the map is one of the supported types.
Behaviors of the casts are shown with the examples below:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'null'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>
<span class="c1">-- NULL</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'1'</span> <span class="k">AS</span> <span class="nb">INTEGER</span><span class="p">);</span>
<span class="c1">-- 1</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'9223372036854775807'</span> <span class="k">AS</span> <span class="nb">BIGINT</span><span class="p">);</span>
<span class="c1">-- 9223372036854775807</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'"abc"'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>
<span class="c1">-- abc</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'true'</span> <span class="k">AS</span> <span class="nb">BOOLEAN</span><span class="p">);</span>
<span class="c1">-- true</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'1.234'</span> <span class="k">AS</span> <span class="n">DOUBLE</span><span class="p">);</span>
<span class="c1">-- 1.234</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[1,23,456]'</span> <span class="k">AS</span> <span class="nb">ARRAY</span><span class="p">(</span><span class="nb">INTEGER</span><span class="p">));</span>
<span class="c1">-- [1, 23, 456]</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[1,null,456]'</span> <span class="k">AS</span> <span class="nb">ARRAY</span><span class="p">(</span><span class="nb">INTEGER</span><span class="p">));</span>
<span class="c1">-- [1, NULL, 456]</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[[1,23],[456]]'</span> <span class="k">AS</span> <span class="nb">ARRAY</span><span class="p">(</span><span class="nb">ARRAY</span><span class="p">(</span><span class="nb">INTEGER</span><span class="p">)));</span>
<span class="c1">-- [[1, 23], [456]]</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'{"k1":1,"k2":23,"k3":456}'</span> <span class="k">AS</span> <span class="k">MAP</span><span class="p">(</span><span class="nb">VARCHAR</span><span class="p">,</span> <span class="nb">INTEGER</span><span class="p">));</span>
<span class="c1">-- {k1=1, k2=23, k3=456}</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'{"v1":123,"v2":"abc","v3":true}'</span> <span class="k">AS</span>
            <span class="k">ROW</span><span class="p">(</span><span class="n">v1</span> <span class="nb">BIGINT</span><span class="p">,</span> <span class="n">v2</span> <span class="nb">VARCHAR</span><span class="p">,</span> <span class="n">v3</span> <span class="nb">BOOLEAN</span><span class="p">));</span>
<span class="c1">-- {v1=123, v2=abc, v3=true}</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[123,"abc",true]'</span> <span class="k">AS</span>
            <span class="k">ROW</span><span class="p">(</span><span class="n">v1</span> <span class="nb">BIGINT</span><span class="p">,</span> <span class="n">v2</span> <span class="nb">VARCHAR</span><span class="p">,</span> <span class="n">v3</span> <span class="nb">BOOLEAN</span><span class="p">));</span>
<span class="c1">-- {v1=123, v2=abc, v3=true}</span>
</pre></div>
</div>
<p>JSON arrays can have mixed element types and JSON maps can have mixed
value types. This makes it impossible to cast them to SQL arrays and maps in
some cases. To address this, Trino supports partial casting of arrays and maps:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[[1, 23], 456]'</span> <span class="k">AS</span> <span class="nb">ARRAY</span><span class="p">(</span><span class="n">JSON</span><span class="p">));</span>
<span class="c1">-- [JSON '[1,23]', JSON '456']</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'{"k1": [1, 23], "k2": 456}'</span> <span class="k">AS</span> <span class="k">MAP</span><span class="p">(</span><span class="nb">VARCHAR</span><span class="p">,</span> <span class="n">JSON</span><span class="p">));</span>
<span class="c1">-- {k1 = JSON '[1,23]', k2 = JSON '456'}</span>

<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[null]'</span> <span class="k">AS</span> <span class="nb">ARRAY</span><span class="p">(</span><span class="n">JSON</span><span class="p">));</span>
<span class="c1">-- [JSON 'null']</span>
</pre></div>
</div>
<p>When casting from <code class="docutils literal notranslate"><span class="pre">JSON</span></code> to <code class="docutils literal notranslate"><span class="pre">ROW</span></code>, both JSON array and JSON object are supported.</p>


<h2 id="json-functions">JSON functions<a class="headerlink" href="#json-functions" title="Permalink to this headline">#</a></h2>
<dl class="py function">
<dt id="is_json_scalar">
<code class="sig-name descname">is_json_scalar</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#is_json_scalar" title="Permalink to this definition">#</a></dt>
<dd><p>Determine if <code class="docutils literal notranslate"><span class="pre">json</span></code> is a scalar (i.e. a JSON number, a JSON string, <code class="docutils literal notranslate"><span class="pre">true</span></code>, <code class="docutils literal notranslate"><span class="pre">false</span></code> or <code class="docutils literal notranslate"><span class="pre">null</span></code>):</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">is_json_scalar</span><span class="p">(</span><span class="s1">'1'</span><span class="p">);</span>         <span class="c1">-- true</span>
<span class="k">SELECT</span> <span class="n">is_json_scalar</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span><span class="p">);</span> <span class="c1">-- false</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_array_contains">
<code class="sig-name descname">json_array_contains</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em>, <em class="sig-param"><span class="n">value</span></em><span class="sig-paren">)</span> → boolean<a class="headerlink" href="#json_array_contains" title="Permalink to this definition">#</a></dt>
<dd><p>Determine if <code class="docutils literal notranslate"><span class="pre">value</span></code> exists in <code class="docutils literal notranslate"><span class="pre">json</span></code> (a string containing a JSON array):</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><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> <span class="c1">-- true</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_array_get">
<code class="sig-name descname">json_array_get</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json_array</span></em>, <em class="sig-param"><span class="n">index</span></em><span class="sig-paren">)</span> → json<a class="headerlink" href="#json_array_get" title="Permalink to this definition">#</a></dt>
<dd><div class="admonition warning">
<p class="admonition-title">Warning</p>
<p>The semantics of this function are broken. If the extracted element
is a string, it will be converted into an invalid <code class="docutils literal notranslate"><span class="pre">JSON</span></code> value that
is not properly quoted (the value will not be surrounded by quotes
and any interior quotes will not be escaped).</p>
<p>We recommend against using this function. It cannot be fixed without
impacting existing usages and may be removed in a future release.</p>
</div>
<p>Returns the element at the specified index into the <code class="docutils literal notranslate"><span class="pre">json_array</span></code>.
The index is zero-based:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["a", [3, 9], "c"]'</span><span class="p">,</span> <span class="mi">0</span><span class="p">);</span> <span class="c1">-- JSON 'a' (invalid JSON)</span>
<span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["a", [3, 9], "c"]'</span><span class="p">,</span> <span class="mi">1</span><span class="p">);</span> <span class="c1">-- JSON '[3,9]'</span>
</pre></div>
</div>
<p>This function also supports negative indexes for fetching element indexed
from the end of an array:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["c", [3, 9], "a"]'</span><span class="p">,</span> <span class="o">-</span><span class="mi">1</span><span class="p">);</span> <span class="c1">-- JSON 'a' (invalid JSON)</span>
<span class="k">SELECT</span> <span class="n">json_array_get</span><span class="p">(</span><span class="s1">'["c", [3, 9], "a"]'</span><span class="p">,</span> <span class="o">-</span><span class="mi">2</span><span class="p">);</span> <span class="c1">-- JSON '[3,9]'</span>
</pre></div>
</div>
<p>If the element at the specified index doesn’t exist, the function returns null:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><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="c1">-- 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="c1">-- 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="c1">-- NULL</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_array_length">
<code class="sig-name descname">json_array_length</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em><span class="sig-paren">)</span> → bigint<a class="headerlink" href="#json_array_length" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the array length of <code class="docutils literal notranslate"><span class="pre">json</span></code> (a string containing a JSON array):</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><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> <span class="c1">-- 3</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_extract">
<code class="sig-name descname">json_extract</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em>, <em class="sig-param"><span class="n">json_path</span></em><span class="sig-paren">)</span> → json<a class="headerlink" href="#json_extract" title="Permalink to this definition">#</a></dt>
<dd><p>Evaluates the <a class="reference external" href="http://goessner.net/articles/JsonPath/">JSONPath</a>-like expression <code class="docutils literal notranslate"><span class="pre">json_path</span></code> on <code class="docutils literal notranslate"><span class="pre">json</span></code>
(a string containing JSON) and returns the result as a JSON string:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><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>
<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>
<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 name"]'</span><span class="p">);</span>
</pre></div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_extract_scalar">
<code class="sig-name descname">json_extract_scalar</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em>, <em class="sig-param"><span class="n">json_path</span></em><span class="sig-paren">)</span> → varchar<a class="headerlink" href="#json_extract_scalar" title="Permalink to this definition">#</a></dt>
<dd><p>Like <a class="reference internal" href="#json_extract" title="json_extract"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_extract()</span></code></a>, but returns the result value as a string (as opposed
to being encoded as JSON). The value referenced by <code class="docutils literal notranslate"><span class="pre">json_path</span></code> must be a
scalar (boolean, number or string).</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><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="py function">
<dt id="json_format">
<code class="sig-name descname">json_format</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em><span class="sig-paren">)</span> → varchar<a class="headerlink" href="#json_format" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the JSON text serialized from the input JSON value.
This is inverse function to <a class="reference internal" href="#json_parse" title="json_parse"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_parse()</span></code></a>.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[1, 2, 3]'</span><span class="p">);</span> <span class="c1">-- '[1,2,3]'</span>
<span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'"a"'</span><span class="p">);</span>       <span class="c1">-- '"a"'</span>
</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p><a class="reference internal" href="#json_format" title="json_format"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_format()</span></code></a> and <code class="docutils literal notranslate"><span class="pre">CAST(json</span> <span class="pre">AS</span> <span class="pre">VARCHAR)</span></code> have completely
different semantics.</p>
<p><a class="reference internal" href="#json_format" title="json_format"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_format()</span></code></a> serializes the input JSON value to JSON text conforming to
<span class="target" id="index-0"></span><a class="rfc reference external" href="https://tools.ietf.org/html/rfc7159.html"><strong>RFC 7159</strong></a>. The JSON value can be a JSON object, a JSON array, a JSON string,
a JSON number, <code class="docutils literal notranslate"><span class="pre">true</span></code>, <code class="docutils literal notranslate"><span class="pre">false</span></code> or <code class="docutils literal notranslate"><span class="pre">null</span></code>.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'{"a": 1, "b": 2}'</span><span class="p">);</span> <span class="c1">-- '{"a":1,"b":2}'</span>
<span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[1, 2, 3]'</span><span class="p">);</span>        <span class="c1">-- '[1,2,3]'</span>
<span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'"abc"'</span><span class="p">);</span>            <span class="c1">-- '"abc"'</span>
<span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'42'</span><span class="p">);</span>               <span class="c1">-- '42'</span>
<span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'true'</span><span class="p">);</span>             <span class="c1">-- 'true'</span>
<span class="k">SELECT</span> <span class="n">json_format</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'null'</span><span class="p">);</span>             <span class="c1">-- 'null'</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">CAST(json</span> <span class="pre">AS</span> <span class="pre">VARCHAR)</span></code> casts the JSON value to the corresponding SQL VARCHAR value.
For JSON string, JSON number, <code class="docutils literal notranslate"><span class="pre">true</span></code>, <code class="docutils literal notranslate"><span class="pre">false</span></code> or <code class="docutils literal notranslate"><span class="pre">null</span></code>, the cast
behavior is same as the corresponding SQL type. JSON object and JSON array
cannot be cast to VARCHAR.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'{"a": 1, "b": 2}'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span> <span class="c1">-- ERROR!</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'[1, 2, 3]'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>        <span class="c1">-- ERROR!</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'"abc"'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>            <span class="c1">-- 'abc' (the double quote is gone)</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'42'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>               <span class="c1">-- '42'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'true'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>             <span class="c1">-- 'true'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="n">JSON</span> <span class="s1">'null'</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">);</span>             <span class="c1">-- NULL</span>
</pre></div>
</div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_parse">
<code class="sig-name descname">json_parse</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">string</span></em><span class="sig-paren">)</span> → json<a class="headerlink" href="#json_parse" title="Permalink to this definition">#</a></dt>
<dd><p>Returns the JSON value deserialized from the input JSON text.
This is inverse function to <a class="reference internal" href="#json_format" title="json_format"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_format()</span></code></a>:</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span><span class="p">);</span>   <span class="c1">-- JSON '[1,2,3]'</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'"abc"'</span><span class="p">);</span>       <span class="c1">-- JSON '"abc"'</span>
</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p><a class="reference internal" href="#json_parse" title="json_parse"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_parse()</span></code></a> and <code class="docutils literal notranslate"><span class="pre">CAST(string</span> <span class="pre">AS</span> <span class="pre">JSON)</span></code> have completely
different semantics.</p>
<p><a class="reference internal" href="#json_parse" title="json_parse"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_parse()</span></code></a> expects a JSON text conforming to <span class="target" id="index-1"></span><a class="rfc reference external" href="https://tools.ietf.org/html/rfc7159.html"><strong>RFC 7159</strong></a>, and returns
the JSON value deserialized from the JSON text.
The JSON value can be a JSON object, a JSON array, a JSON string, a JSON number,
<code class="docutils literal notranslate"><span class="pre">true</span></code>, <code class="docutils literal notranslate"><span class="pre">false</span></code> or <code class="docutils literal notranslate"><span class="pre">null</span></code>.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'not_json'</span><span class="p">);</span>         <span class="c1">-- ERROR!</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'["a": 1, "b": 2]'</span><span class="p">);</span> <span class="c1">-- JSON '["a": 1, "b": 2]'</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span><span class="p">);</span>        <span class="c1">-- JSON '[1,2,3]'</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'"abc"'</span><span class="p">);</span>            <span class="c1">-- JSON '"abc"'</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'42'</span><span class="p">);</span>               <span class="c1">-- JSON '42'</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'true'</span><span class="p">);</span>             <span class="c1">-- JSON 'true'</span>
<span class="k">SELECT</span> <span class="n">json_parse</span><span class="p">(</span><span class="s1">'null'</span><span class="p">);</span>             <span class="c1">-- JSON 'null'</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">CAST(string</span> <span class="pre">AS</span> <span class="pre">JSON)</span></code> takes any VARCHAR value as input, and returns
a JSON string with its value set to input string.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'not_json'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>         <span class="c1">-- JSON '"not_json"'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'["a": 1, "b": 2]'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span> <span class="c1">-- JSON '"[\"a\": 1, \"b\": 2]"'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'[1, 2, 3]'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>        <span class="c1">-- JSON '"[1, 2, 3]"'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'"abc"'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>            <span class="c1">-- JSON '"\"abc\""'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'42'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>               <span class="c1">-- JSON '"42"'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'true'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>             <span class="c1">-- JSON '"true"'</span>
<span class="k">SELECT</span> <span class="k">CAST</span><span class="p">(</span><span class="s1">'null'</span> <span class="k">AS</span> <span class="n">JSON</span><span class="p">);</span>             <span class="c1">-- JSON '"null"'</span>
</pre></div>
</div>
</div>
</dd></dl>
<dl class="py function">
<dt id="json_size">
<code class="sig-name descname">json_size</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">json</span></em>, <em class="sig-param"><span class="n">json_path</span></em><span class="sig-paren">)</span> → bigint<a class="headerlink" href="#json_size" title="Permalink to this definition">#</a></dt>
<dd><p>Like <a class="reference internal" href="#json_extract" title="json_extract"><code class="xref py py-func docutils literal notranslate"><span class="pre">json_extract()</span></code></a>, but returns the size of the value.
For objects or arrays, the size is the number of members,
and the size of a scalar value is zero.</p>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></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'</span><span class="p">);</span>   <span class="c1">-- 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="c1">-- 3</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="c1">-- 0</span>
</pre></div>
</div>
</dd></dl>


          </article>
        </div>
      </div>
    </main>
  </div>
  <footer class="md-footer">
    <div class="md-footer-nav">
      <nav class="md-footer-nav__inner md-grid">
          
            <a href="ipaddress.html" title="IP Address Functions"
               class="md-flex md-footer-nav__link md-footer-nav__link--prev"
               rel="prev">
              <div class="md-flex__cell md-flex__cell--shrink">
                <i class="md-icon md-icon--arrow-back md-footer-nav__button"></i>
              </div>
              <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
                <span class="md-flex__ellipsis">
                  <span
                      class="md-footer-nav__direction"> Previous </span> IP Address Functions </span>
              </div>
            </a>
          
          
            <a href="lambda.html" title="Lambda expressions"
               class="md-flex md-footer-nav__link md-footer-nav__link--next"
               rel="next">
            <div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title"><span
                class="md-flex__ellipsis"> <span
                class="md-footer-nav__direction"> Next </span> Lambda expressions </span>
            </div>
            <div class="md-flex__cell md-flex__cell--shrink"><i
                class="md-icon md-icon--arrow-forward md-footer-nav__button"></i>
            </div>
          
        </a>
        
      </nav>
    </div>
    <div class="md-footer-meta md-typeset">
      <div class="md-footer-meta__inner md-grid">
        <div class="md-footer-copyright">
        </div>
      </div>
    </div>
  </footer>
  <script src="../_static/javascripts/application.js"></script>
  <script>app.initialize({version: "1.0.4", url: {base: ".."}})</script>
  </body>
</html>