docs/26.0.0/querying/nested-columns.html - druid-website - Git at Google

 <!DOCTYPE html><html lang="en"><head><meta charSet="utf-8"/><meta http-equiv="X-UA-Compatible" content="IE=edge"/><title>Nested columns · Apache Druid</title><meta name="viewport" content="width=device-width, initial-scale=1.0"/><link rel="canonical" href="https://druid.apache.org/docs/26.0.0/querying/nested-columns.html"/><meta name="generator" content="Docusaurus"/><meta name="description" content="&lt;!--"/><meta name="docsearch:language" content="en"/><meta name="docsearch:version" content="26.0.0" /><meta property="og:title" content="Nested columns · Apache Druid"/><meta property="og:type" content="website"/><meta property="og:url" content="https://druid.apache.org/index.html"/><meta property="og:description" content="&lt;!--"/><meta property="og:image" content="https://druid.apache.org/img/druid_nav.png"/><meta name="twitter:card" content="summary"/><meta name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"/><link rel="shortcut icon" href="/img/favicon.png"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.css"/><link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/default.min.css"/><script async="" src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script><script>
               window.dataLayer = window.dataLayer || [];
               function gtag(){dataLayer.push(arguments); }
               gtag('js', new Date());
               gtag('config', 'UA-131010415-1');
             </script><link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css"/><link rel="stylesheet" href="/css/code-block-buttons.css"/><script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/2.0.4/clipboard.min.js"></script><script type="text/javascript" src="/js/code-block-buttons.js"></script><script src="/js/scrollSpy.js"></script><link rel="stylesheet" href="/css/main.css"/><script src="/js/codetabs.js"></script></head><body class="sideNavVisible separateOnPageNav"><div class="fixedHeaderContainer"><div class="headerWrapper wrapper"><header><a href="/"><img class="logo" src="/img/druid_nav.png" alt="Apache Druid"/></a><div class="navigationWrapper navigationSlider"><nav class="slidingNav"><ul class="nav-site nav-site-internal"><li class=""><a href="/technology" target="_self">Technology</a></li><li class=""><a href="/use-cases" target="_self">Use Cases</a></li><li class=""><a href="/druid-powered" target="_self">Powered By</a></li><li class="siteNavGroupActive"><a href="/docs/26.0.0/design/index.html" target="_self">Docs</a></li><li class=""><a href="/community/" target="_self">Community</a></li><li class=""><a href="https://www.apache.org" target="_self">Apache</a></li><li class=""><a href="/downloads.html" target="_self">Download</a></li><li class="navSearchWrapper reactNavSearchWrapper"><input type="text" id="search_input_react" placeholder="Search" title="Search"/></li></ul></nav></div></header></div></div><div class="navPusher"><div class="docMainWrapper wrapper"><div class="docsNavContainer" id="docsNav"><nav class="toc"><div class="toggleNav"><section class="navWrapper wrapper"><div class="navBreadcrumb wrapper"><div class="navToggle" id="navToggler"><div class="hamburger-menu"><div class="line1"></div><div class="line2"></div><div class="line3"></div></div></div><h2><i>›</i><span>Concepts</span></h2><div class="tocToggler" id="tocToggler"><i class="icon-toc"></i></div></div><div class="navGroups"><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Getting started<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/index.html">Introduction to Apache Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/index.html">Quickstart (local)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/single-server.html">Single server deployment</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/cluster.html">Clustered deployment</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Tutorials<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-batch.html">Load files natively</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-msq-extern.html">Load files using SQL 🆕</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-kafka.html">Load from Apache Kafka</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-batch-hadoop.html">Load from Apache Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-query.html">Querying data</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-rollup.html">Roll-up</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-sketches-theta.html">Theta sketches</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-retention.html">Configuring data retention</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-update-data.html">Updating existing data</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-compaction.html">Compacting segments</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-delete-data.html">Deleting data</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-ingestion-spec.html">Writing an ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-transform-spec.html">Transforming input data</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/docker.html">Tutorial: Run with Docker</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-kerberos-hadoop.html">Kerberized HDFS deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-msq-convert-spec.html">Convert ingestion spec to SQL</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-sql-query-view.html">Get to know Query view</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-unnest-arrays.html">Unnesting arrays</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-jupyter-index.html">Jupyter Notebook tutorials</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-jupyter-docker.html">Docker for tutorials</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/tutorials/tutorial-jdbc.html">JDBC connector</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Design<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/architecture.html">Design</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/segments.html">Segments</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/processes.html">Processes and servers</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/dependencies/deep-storage.html">Deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/dependencies/metadata-storage.html">Metadata storage</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/dependencies/zookeeper.html">ZooKeeper</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Ingestion<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/index.html">Ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/data-formats.html">Data formats</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/data-model.html">Data model</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/rollup.html">Data rollup</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/partitioning.html">Partitioning</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/ingestion-spec.html">Ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/schema-design.html">Schema design tips</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Stream ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/kafka-ingestion.html">Apache Kafka ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/kafka-supervisor-reference.html">Apache Kafka supervisor</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/kafka-supervisor-operations.html">Apache Kafka operations</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/kinesis-ingestion.html">Amazon Kinesis</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Batch ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/native-batch.html">Native batch</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/native-batch-input-sources.html">Native batch: input sources</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/migrate-from-firehose.html">Migrate from firehose</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/hadoop.html">Hadoop-based</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">SQL-based ingestion 🆕</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/index.html">Overview</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/concepts.html">Key concepts</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/api.html">API</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/security.html">Security</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/examples.html">Examples</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/reference.html">Reference</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/multi-stage-query/known-issues.html">Known issues</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/tasks.html">Task reference</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/faq.html">Troubleshooting FAQ</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Data management<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/data-management/index.html">Overview</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/data-management/update.html">Data updates</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/data-management/delete.html">Data deletion</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/data-management/schema-changes.html">Schema changes</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/data-management/compaction.html">Compaction</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/data-management/automatic-compaction.html">Automatic compaction</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Querying<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Druid SQL</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql.html">Overview and syntax</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-data-types.html">SQL data types</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-operators.html">Operators</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-scalar.html">Scalar functions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-aggregations.html">Aggregation functions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-multivalue-string-functions.html">Multi-value string functions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-json-functions.html">JSON functions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-functions.html">All functions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-api.html">Druid SQL API</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-jdbc.html">JDBC driver API</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-query-context.html">SQL query context</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-metadata-tables.html">SQL metadata tables</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sql-translation.html">SQL query translation</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/querying.html">Native queries</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/query-execution.html">Query execution</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/troubleshooting.html">Troubleshooting</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Concepts</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/datasource.html">Datasources</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/joins.html">Joins</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/lookups.html">Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/multi-value-dimensions.html">Multi-value dimensions</a></li><li class="navListItem navListItemActive"><a class="navItem" href="/docs/26.0.0/querying/nested-columns.html">Nested columns</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/multitenancy.html">Multitenancy</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/caching.html">Query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/using-caching.html">Using query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/query-context.html">Query context</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query types</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/timeseriesquery.html">Timeseries</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/topnquery.html">TopN</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/groupbyquery.html">GroupBy</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/scan-query.html">Scan</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/searchquery.html">Search</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/timeboundaryquery.html">TimeBoundary</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/segmentmetadataquery.html">SegmentMetadata</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/datasourcemetadataquery.html">DatasourceMetadata</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query components</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/filters.html">Filters</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/granularities.html">Granularities</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/dimensionspecs.html">Dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/aggregations.html">Aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/post-aggregations.html">Post-aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/misc/math-expr.html">Expressions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/having.html">Having filters (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/limitspec.html">Sorting and limiting (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/topnmetricspec.html">Sorting (topN)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/sorting-orders.html">String comparators</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/virtual-columns.html">Virtual columns</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/geo.html">Spatial filters</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Configuration<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/configuration/index.html">Configuration reference</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions.html">Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/configuration/logging.html">Logging</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Operations<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/web-console.html">Web console</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/java.html">Java runtime</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Security</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/security-overview.html">Security overview</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/security-user-auth.html">User authentication and authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/auth-ldap.html">LDAP auth</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/password-provider.html">Password providers</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/dynamic-config-provider.html">Dynamic Config Providers</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/tls-support.html">TLS support</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Performance tuning</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/basic-cluster-tuning.html">Basic cluster tuning</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/segment-optimization.html">Segment size optimization</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/mixed-workloads.html">Mixed workloads</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/http-compression.html">HTTP compression</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/clean-metadata-store.html">Automated metadata cleanup</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Monitoring</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/request-logging.html">Request logging</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/metrics.html">Metrics</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/alerts.html">Alerts</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/api-reference.html">API reference</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/high-availability.html">High availability</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/rolling-updates.html">Rolling updates</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/rule-configuration.html">Using rules to drop and retain data</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/other-hadoop.html">Working with different versions of Apache Hadoop</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Misc</h4><ul><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/dump-segment.html">dump-segment tool</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/reset-cluster.html">reset-cluster tool</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/insert-segment-to-db.html">insert-segment-to-db tool</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/pull-deps.html">pull-deps tool</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/deep-storage-migration.html">Deep storage migration</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/export-metadata.html">Export Metadata Tool</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/metadata-migration.html">Metadata Migration</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/use_sbt_to_build_fat_jar.html">Content for build.sbt</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Development<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/overview.html">Developing on Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/modules.html">Creating extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/javascript.html">JavaScript functionality</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/build.html">Build from source</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/versioning.html">Versioning</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/experimental.html">Experimental features</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Misc<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/misc/papers-and-talks.html">Papers</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Hidden<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/26.0.0/comparisons/druid-vs-elasticsearch.html">Apache Druid vs Elasticsearch</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/comparisons/druid-vs-key-value.html">Apache Druid vs. Key/Value Stores (HBase/Cassandra/OpenTSDB)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/comparisons/druid-vs-kudu.html">Apache Druid vs Kudu</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/comparisons/druid-vs-redshift.html">Apache Druid vs Redshift</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/comparisons/druid-vs-spark.html">Apache Druid vs Spark</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/comparisons/druid-vs-sql-on-hadoop.html">Apache Druid vs SQL-on-Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/auth.html">Authentication and Authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/broker.html">Broker</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/coordinator.html">Coordinator Process</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/historical.html">Historical Process</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/indexer.html">Indexer Process</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/indexing-service.html">Indexing Service</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/middlemanager.html">MiddleManager Process</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/overlord.html">Overlord Process</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/router.html">Router Process</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/design/peons.html">Peons</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/approximate-histograms.html">Approximate Histogram aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/avro.html">Apache Avro</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/azure.html">Microsoft Azure</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/bloom-filter.html">Bloom Filter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/datasketches-extension.html">DataSketches extension</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/datasketches-hll.html">DataSketches HLL Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/datasketches-quantiles.html">DataSketches Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/datasketches-theta.html">DataSketches Theta Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/datasketches-tuple.html">DataSketches Tuple Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/druid-basic-security.html">Basic Security</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/druid-kerberos.html">Kerberos</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/druid-lookups.html">Cached Lookup Module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/druid-ranger-security.html">Apache Ranger Security</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/google.html">Google Cloud Storage</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/hdfs.html">HDFS</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/kafka-extraction-namespace.html">Apache Kafka Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/lookups-cached-global.html">Globally Cached Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/mysql.html">MySQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/orc.html">ORC Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/druid-pac4j.html">Druid pac4j based Security extension</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/parquet.html">Apache Parquet Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/postgresql.html">PostgreSQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/protobuf.html">Protobuf</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/s3.html">S3-compatible</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/simple-client-sslcontext.html">Simple SSLContext Provider Module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/stats.html">Stats aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/test-stats.html">Test Stats Aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/druid-aws-rds.html">Druid AWS RDS Module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-core/kubernetes.html">Kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/ambari-metrics-emitter.html">Ambari Metrics Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/cassandra.html">Apache Cassandra</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/cloudfiles.html">Rackspace Cloud Files</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/distinctcount.html">DistinctCount Aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/graphite.html">Graphite Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/influx.html">InfluxDB Line Protocol Parser</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/influxdb-emitter.html">InfluxDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/kafka-emitter.html">Kafka Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/materialized-view.html">Materialized View</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/momentsketch-quantiles.html">Moment Sketches for Approximate Quantiles module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/moving-average-query.html">Moving Average Query</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/opentsdb-emitter.html">OpenTSDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/redis-cache.html">Druid Redis Cache</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/sqlserver.html">Microsoft SQLServer</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/statsd.html">StatsD Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/tdigestsketch-quantiles.html">T-Digest Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/thrift.html">Thrift</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/time-min-max.html">Timestamp Min/Max aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/gce-extensions.html">GCE Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/aliyun-oss.html">Aliyun OSS</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/development/extensions-contrib/prometheus.html">Prometheus Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/operations/kubernetes.html">kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/hll-old.html">Cardinality/HyperUnique aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/querying/select-query.html">Select</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/native-batch-firehose.html">Firehose (deprecated)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/native-batch-simple-task.html">Native batch (simple)</a></li><li class="navListItem"><a class="navItem" href="/docs/26.0.0/ingestion/standalone-realtime.html">Realtime Process</a></li></ul></div></div></section></div><script>
             var coll = document.getElementsByClassName('collapsible');
             var checkActiveCategory = true;
             for (var i = 0; i < coll.length; i++) {
               var links = coll[i].nextElementSibling.getElementsByTagName('*');
               if (checkActiveCategory){
                 for (var j = 0; j < links.length; j++) {
                   if (links[j].classList.contains('navListItemActive')){
                     coll[i].nextElementSibling.classList.toggle('hide');
                     coll[i].childNodes[1].classList.toggle('rotate');
                     checkActiveCategory = false;
                     break;
                   }
                 }
               }

               coll[i].addEventListener('click', function() {
                 var arrow = this.childNodes[1];
                 arrow.classList.toggle('rotate');
                 var content = this.nextElementSibling;
                 content.classList.toggle('hide');
               });
             }

             document.addEventListener('DOMContentLoaded', function() {
               createToggler('#navToggler', '#docsNav', 'docsSliderActive');
               createToggler('#tocToggler', 'body', 'tocActive');

               var headings = document.querySelector('.toc-headings');
               headings && headings.addEventListener('click', function(event) {
                 var el = event.target;
                 while(el !== headings){
                   if (el.tagName === 'A') {
                     document.body.classList.remove('tocActive');
                     break;
                   } else{
                     el = el.parentNode;
                   }
                 }
               }, false);

               function createToggler(togglerSelector, targetSelector, className) {
                 var toggler = document.querySelector(togglerSelector);
                 var target = document.querySelector(targetSelector);

                 if (!toggler) {
                   return;
                 }

                 toggler.onclick = function(event) {
                   event.preventDefault();

                   target.classList.toggle(className);
                 };
               }
             });
         </script></nav></div><div class="container mainContainer docsContainer"><div class="wrapper"><div class="post"><header class="postHeader"><a class="edit-page-link button" href="https://github.com/apache/druid/edit/master/docs/querying/nested-columns.md" target="_blank" rel="noreferrer noopener">Edit</a><h1 id="__docusaurus" class="postHeaderTitle">Nested columns</h1></header><article><div><span><!--
   ~ Licensed to the Apache Software Foundation (ASF) under one
   ~ or more contributor license agreements.  See the NOTICE file
   ~ distributed with this work for additional information
   ~ regarding copyright ownership.  The ASF licenses this file
   ~ to you under the Apache License, Version 2.0 (the
   ~ "License"); you may not use this file except in compliance
   ~ with the License.  You may obtain a copy of the License at
   ~
   ~   http://www.apache.org/licenses/LICENSE-2.0
   ~
   ~ Unless required by applicable law or agreed to in writing,
   ~ software distributed under the License is distributed on an
   ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   ~ KIND, either express or implied.  See the License for the
   ~ specific language governing permissions and limitations
   ~ under the License.
   -->
 <p>Apache Druid supports directly storing nested data structures in <code>COMPLEX&lt;json&gt;</code> columns. <code>COMPLEX&lt;json&gt;</code> columns store a copy of the structured data in JSON format and specialized internal columns and indexes for nested literal values—STRING, LONG, and DOUBLE types. An optimized <a href="/docs/26.0.0/querying/virtual-columns.html#nested-field-virtual-column">virtual column</a> allows Druid to read and filter these values at speeds consistent with standard Druid LONG, DOUBLE, and STRING columns.</p>
 <p>Druid <a href="/docs/26.0.0/querying/sql-json-functions.html">SQL JSON functions</a> allow you to extract, transform, and create <code>COMPLEX&lt;json&gt;</code> values in SQL queries, using the specialized virtual columns where appropriate. You can use the <a href="/docs/26.0.0/misc/math-expr.html#json-functions">JSON nested columns functions</a> in <a href="/docs/26.0.0/querying/querying.html">native queries</a> using <a href="/docs/26.0.0/querying/virtual-columns.html#expression-virtual-column">expression virtual columns</a>, and in native ingestion with a <a href="/docs/26.0.0/ingestion/ingestion-spec.html#transformspec"><code>transformSpec</code></a>.</p>
 <p>You can use the JSON functions in INSERT and REPLACE statements in SQL-based ingestion, or in a <code>transformSpec</code> in native ingestion as an alternative to using a <a href="/docs/26.0.0/ingestion/data-formats.html#flattenspec"><code>flattenSpec</code></a> object to &quot;flatten&quot; nested data for ingestion.</p>
 <p>Druid supports directly ingesting nested data with the following formats: JSON, Parquet, Avro, ORC, Protobuf.</p>
 <h2><a class="anchor" aria-hidden="true" id="example-nested-data"></a><a href="#example-nested-data" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example nested data</h2>
 <p>The examples in this topic use the JSON data in <a href="https://static.imply.io/data/nested_example_data.json"><code>nested_example_data.json</code></a>. The file contains a simple facsimile of an order tracking and shipping table.</p>
 <p>When pretty-printed, a sample row in <code>nested_example_data</code> looks like this:</p>
 <pre><code class="hljs css language-json">{
     "time":"2022-6-14T10:32:08Z",
     "product":"Keyboard",
     "department":"Computers",
     "shipTo":{
         "firstName": "Sandra",
         "lastName": "Beatty",
         "address": {
             "street": "293 Grant Well",
             "city": "Loischester",
             "state": "FL",
             "country": "TV",
             "postalCode": "88845-0066"
         },
         "phoneNumbers": [
             {"type":"primary","number":"1-788-771-7028 x8627" },
             {"type":"secondary","number":"1-460-496-4884 x887"}
         ]
     },
     "details"{"color":"plum","price":"40.00"}
 }
 </code></pre>
 <h2><a class="anchor" aria-hidden="true" id="native-batch-ingestion"></a><a href="#native-batch-ingestion" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Native batch ingestion</h2>
 <p>For native batch ingestion, you can use the <a href="/docs/26.0.0/querying/sql-json-functions.html">SQL JSON functions</a> to extract nested data as an alternative to using the <a href="/docs/26.0.0/ingestion/data-formats.html#flattenspec"><code>flattenSpec</code></a> input format.</p>
 <p>To configure a dimension as a nested data type, specify the <code>json</code> type for the dimension in the <code>dimensions</code> list in the <code>dimensionsSpec</code> property of your ingestion spec.</p>
 <p>For example, the following ingestion spec instructs Druid to ingest <code>shipTo</code> and <code>details</code> as JSON-type nested dimensions:</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,
   <span class="hljs-attr">"spec"</span>: {
     <span class="hljs-attr">"ioConfig"</span>: {
       <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,
       <span class="hljs-attr">"inputSource"</span>: {
         <span class="hljs-attr">"type"</span>: <span class="hljs-string">"http"</span>,
         <span class="hljs-attr">"uris"</span>: [
           <span class="hljs-string">"https://static.imply.io/data/nested_example_data.json"</span>
         ]
       },
       <span class="hljs-attr">"inputFormat"</span>: {
         <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>
       }
     },
     <span class="hljs-attr">"dataSchema"</span>: {
       <span class="hljs-attr">"granularitySpec"</span>: {
         <span class="hljs-attr">"segmentGranularity"</span>: <span class="hljs-string">"day"</span>,
         <span class="hljs-attr">"queryGranularity"</span>: <span class="hljs-string">"none"</span>,
         <span class="hljs-attr">"rollup"</span>: <span class="hljs-literal">false</span>
       },
       <span class="hljs-attr">"dataSource"</span>: <span class="hljs-string">"nested_data_example"</span>,
       <span class="hljs-attr">"timestampSpec"</span>: {
         <span class="hljs-attr">"column"</span>: <span class="hljs-string">"time"</span>,
         <span class="hljs-attr">"format"</span>: <span class="hljs-string">"auto"</span>
       },
       <span class="hljs-attr">"dimensionsSpec"</span>: {
         <span class="hljs-attr">"dimensions"</span>: [
           <span class="hljs-string">"product"</span>,
           <span class="hljs-string">"department"</span>,
           {
             <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
             <span class="hljs-attr">"name"</span>: <span class="hljs-string">"shipTo"</span>
           },
           {
             <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
             <span class="hljs-attr">"name"</span>: <span class="hljs-string">"details"</span>
           }
         ]
       },
       <span class="hljs-attr">"transformSpec"</span>: {}
     },
     <span class="hljs-attr">"tuningConfig"</span>: {
       <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,
       <span class="hljs-attr">"partitionsSpec"</span>: {
         <span class="hljs-attr">"type"</span>: <span class="hljs-string">"dynamic"</span>
       }
     }
   }
 }
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="transform-data-during-batch-ingestion"></a><a href="#transform-data-during-batch-ingestion" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Transform data during batch ingestion</h3>
 <p>You can use the <a href="/docs/26.0.0/querying/sql-json-functions.html">SQL JSON functions</a> to transform nested data and reference the transformed data in your ingestion spec.</p>
 <p>To do this, define the output name and expression in the <code>transforms</code> list in the <code>transformSpec</code> object of your ingestion spec.</p>
 <p>For example, the following ingestion spec extracts <code>firstName</code>, <code>lastName</code> and <code>address</code> from <code>shipTo</code> and creates a composite JSON object containing <code>product</code>, <code>details</code> and <code>department</code>.</p>
 <pre><code class="hljs css language-json">{
   <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,
   <span class="hljs-attr">"spec"</span>: {
     <span class="hljs-attr">"ioConfig"</span>: {
       <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,
       <span class="hljs-attr">"inputSource"</span>: {
         <span class="hljs-attr">"type"</span>: <span class="hljs-string">"http"</span>,
         <span class="hljs-attr">"uris"</span>: [
           <span class="hljs-string">"https://static.imply.io/data/nested_example_data.json"</span>
         ]
       },
       <span class="hljs-attr">"inputFormat"</span>: {
         <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>
       }
     },
     <span class="hljs-attr">"dataSchema"</span>: {
       <span class="hljs-attr">"granularitySpec"</span>: {
         <span class="hljs-attr">"segmentGranularity"</span>: <span class="hljs-string">"day"</span>,
         <span class="hljs-attr">"queryGranularity"</span>: <span class="hljs-string">"none"</span>,
         <span class="hljs-attr">"rollup"</span>: <span class="hljs-literal">false</span>
       },
       <span class="hljs-attr">"dataSource"</span>: <span class="hljs-string">"nested_data_transform_example"</span>,
       <span class="hljs-attr">"timestampSpec"</span>: {
         <span class="hljs-attr">"column"</span>: <span class="hljs-string">"time"</span>,
         <span class="hljs-attr">"format"</span>: <span class="hljs-string">"auto"</span>
       },
       <span class="hljs-attr">"dimensionsSpec"</span>: {
         <span class="hljs-attr">"dimensions"</span>: [
           <span class="hljs-string">"firstName"</span>,
           <span class="hljs-string">"lastName"</span>,
           {
             <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
             <span class="hljs-attr">"name"</span>: <span class="hljs-string">"address"</span>
           },
           {
             <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
             <span class="hljs-attr">"name"</span>: <span class="hljs-string">"productDetails"</span>
           }
         ]
       },
       <span class="hljs-attr">"transformSpec"</span>: {
         <span class="hljs-attr">"transforms"</span>:[
             { <span class="hljs-attr">"type"</span>:<span class="hljs-string">"expression"</span>, <span class="hljs-attr">"name"</span>:<span class="hljs-string">"firstName"</span>, <span class="hljs-attr">"expression"</span>:<span class="hljs-string">"json_value(shipTo, '$.firstName')"</span>},
             { <span class="hljs-attr">"type"</span>:<span class="hljs-string">"expression"</span>, <span class="hljs-attr">"name"</span>:<span class="hljs-string">"lastName"</span>, <span class="hljs-attr">"expression"</span>:<span class="hljs-string">"json_value(shipTo, '$.lastName')"</span>},
             { <span class="hljs-attr">"type"</span>:<span class="hljs-string">"expression"</span>, <span class="hljs-attr">"name"</span>:<span class="hljs-string">"address"</span>, <span class="hljs-attr">"expression"</span>:<span class="hljs-string">"json_query(shipTo, '$.address')"</span>},
             { <span class="hljs-attr">"type"</span>:<span class="hljs-string">"expression"</span>, <span class="hljs-attr">"name"</span>:<span class="hljs-string">"productDetails"</span>, <span class="hljs-attr">"expression"</span>:<span class="hljs-string">"json_object('product', product, 'details', details, 'department', department)"</span>}
         ]
       }
     },
     <span class="hljs-attr">"tuningConfig"</span>: {
       <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,
       <span class="hljs-attr">"partitionsSpec"</span>: {
         <span class="hljs-attr">"type"</span>: <span class="hljs-string">"dynamic"</span>
       }
     }
   }
 }
 </code></pre>
 <h2><a class="anchor" aria-hidden="true" id="sql-based-ingestion"></a><a href="#sql-based-ingestion" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>SQL-based ingestion</h2>
 <p>To ingest nested data using SQL-based ingestion, specify <code>COMPLEX&lt;json&gt;</code> as the value for <code>type</code> when you define the row signature—<code>shipTo</code> and <code>details</code> in the following example ingestion spec:</p>
 <p><img src="../assets/nested-msq-ingestion.png" alt="SQL-based ingestion"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">INTO</span> msq_nested_data_example OVERWRITE <span class="hljs-keyword">ALL</span>
 <span class="hljs-keyword">SELECT</span>
   TIME_PARSE(<span class="hljs-string">"time"</span>) <span class="hljs-keyword">as</span> __time,
   product,
   department,
   shipTo,
   details
 <span class="hljs-keyword">FROM</span> (
   <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span>
   <span class="hljs-keyword">TABLE</span>(
     EXTERN(
       <span class="hljs-string">'{"type":"http","uris":["https://static.imply.io/data/nested_example_data.json"]}'</span>,
       <span class="hljs-string">'{"type":"json"}'</span>,
       <span class="hljs-string">'[{"name":"time","type":"string"},{"name":"product","type":"string"},{"name":"department","type":"string"},{"name":"shipTo","type":"COMPLEX&lt;json&gt;"},{"name":"details","type":"COMPLEX&lt;json&gt;"}]'</span>
     )
   )
 )
 PARTITIONED <span class="hljs-keyword">BY</span> <span class="hljs-keyword">ALL</span>
 </code></pre>
 <h2><a class="anchor" aria-hidden="true" id="streaming-ingestion"></a><a href="#streaming-ingestion" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Streaming ingestion</h2>
 <p>You can ingest nested data into Druid using the <a href="/docs/26.0.0/ingestion/index.html#streaming">streaming method</a>—for example, from a Kafka topic.</p>
 <p>When you <a href="/docs/26.0.0/development/extensions-core/kafka-ingestion.html#define-a-supervisor-spec">define your supervisor spec</a>, include a dimension with type <code>json</code> for each nested column. For example, the following supervisor spec from the <a href="/docs/26.0.0/tutorials/tutorial-kafka.html">Kafka ingestion tutorial</a> contains dimensions for the nested columns <code>event</code>, <code>agent</code>, and <code>geo_ip</code> in datasource <code>kttm-kafka</code>.</p>
 <pre><code class="hljs css language-json">{
    <span class="hljs-attr">"type"</span>: <span class="hljs-string">"kafka"</span>,
    <span class="hljs-attr">"spec"</span>: {
       <span class="hljs-attr">"ioConfig"</span>: {
          <span class="hljs-attr">"type"</span>: <span class="hljs-string">"kafka"</span>,
          <span class="hljs-attr">"consumerProperties"</span>: {
            <span class="hljs-attr">"bootstrap.servers"</span>: <span class="hljs-string">"localhost:9092"</span>
       },
       <span class="hljs-attr">"topic"</span>: <span class="hljs-string">"kttm"</span>,
       <span class="hljs-attr">"inputFormat"</span>: {
          <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>
       },
       <span class="hljs-attr">"useEarliestOffset"</span>: <span class="hljs-literal">true</span>
    },
    <span class="hljs-attr">"tuningConfig"</span>: {
      <span class="hljs-attr">"type"</span>: <span class="hljs-string">"kafka"</span>
    },
    <span class="hljs-attr">"dataSchema"</span>: {
       <span class="hljs-attr">"dataSource"</span>: <span class="hljs-string">"kttm-kafka"</span>,
       <span class="hljs-attr">"timestampSpec"</span>: {
          <span class="hljs-attr">"column"</span>: <span class="hljs-string">"timestamp"</span>,
          <span class="hljs-attr">"format"</span>: <span class="hljs-string">"iso"</span>
       },
       <span class="hljs-attr">"dimensionsSpec"</span>: {
          <span class="hljs-attr">"dimensions"</span>: [
             <span class="hljs-string">"session"</span>,
              <span class="hljs-string">"number"</span>,
              <span class="hljs-string">"client_ip"</span>,
              <span class="hljs-string">"language"</span>,
              <span class="hljs-string">"adblock_list"</span>,
              <span class="hljs-string">"app_version"</span>,
              <span class="hljs-string">"path"</span>,
              <span class="hljs-string">"loaded_image"</span>,
              <span class="hljs-string">"referrer"</span>,
              <span class="hljs-string">"referrer_host"</span>,
              <span class="hljs-string">"server_ip"</span>,
              <span class="hljs-string">"screen"</span>,
              <span class="hljs-string">"window"</span>,
              {
                <span class="hljs-attr">"type"</span>: <span class="hljs-string">"long"</span>,
                <span class="hljs-attr">"name"</span>: <span class="hljs-string">"session_length"</span>
              },
              <span class="hljs-string">"timezone"</span>,
              <span class="hljs-string">"timezone_offset"</span>,
              {
                <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
                <span class="hljs-attr">"name"</span>: <span class="hljs-string">"event"</span>
              },
              {
                <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
                <span class="hljs-attr">"name"</span>: <span class="hljs-string">"agent"</span>
              },
              {
                <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span>,
                <span class="hljs-attr">"name"</span>: <span class="hljs-string">"geo_ip"</span>
              }
            ]
          },
       <span class="hljs-attr">"granularitySpec"</span>: {
          <span class="hljs-attr">"queryGranularity"</span>: <span class="hljs-string">"none"</span>,
          <span class="hljs-attr">"rollup"</span>: <span class="hljs-literal">false</span>,
          <span class="hljs-attr">"segmentGranularity"</span>: <span class="hljs-string">"day"</span>
       }
     }
   }
 }
 </code></pre>
 <p>The <a href="/docs/26.0.0/tutorials/tutorial-kafka.html">Kafka tutorial</a> guides you through the steps to load sample nested data into a Kafka topic, then ingest the data into Druid.</p>
 <h3><a class="anchor" aria-hidden="true" id="transform-data-during-sql-based-ingestion"></a><a href="#transform-data-during-sql-based-ingestion" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Transform data during SQL-based ingestion</h3>
 <p>You can use the <a href="/docs/26.0.0/querying/sql-json-functions.html">SQL JSON functions</a> to transform nested data in your ingestion query.</p>
 <p>For example, the following ingestion query is the SQL-based version of the <a href="#transform-data-during-batch-ingestion">previous batch example</a>—it extracts <code>firstName</code>, <code>lastName</code>, and <code>address</code> from <code>shipTo</code> and creates a composite JSON object containing <code>product</code>, <code>details</code>, and <code>department</code>.</p>
 <p><img src="../assets/nested-msq-ingestion-transform.png" alt="SQL-based ingestion"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">INTO</span> msq_nested_data_transform_example OVERWRITE <span class="hljs-keyword">ALL</span>
 <span class="hljs-keyword">SELECT</span>
   TIME_PARSE(<span class="hljs-string">"time"</span>) <span class="hljs-keyword">as</span> __time,
   JSON_VALUE(shipTo, <span class="hljs-string">'$.firstName'</span>) <span class="hljs-keyword">as</span> firstName,
   JSON_VALUE(shipTo, <span class="hljs-string">'$.lastName'</span>) <span class="hljs-keyword">as</span> lastName,
   JSON_QUERY(shipTo, <span class="hljs-string">'$.address'</span>) <span class="hljs-keyword">as</span> address,
   JSON_OBJECT(<span class="hljs-string">'product'</span>:product,<span class="hljs-string">'details'</span>:details, <span class="hljs-string">'department'</span>:department) <span class="hljs-keyword">as</span> productDetails
 <span class="hljs-keyword">FROM</span> (
   <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span>
   <span class="hljs-keyword">TABLE</span>(
     EXTERN(
       <span class="hljs-string">'{"type":"http","uris":["https://static.imply.io/data/nested_example_data.json"]}'</span>,
       <span class="hljs-string">'{"type":"json"}'</span>,
       <span class="hljs-string">'[{"name":"time","type":"string"},{"name":"product","type":"string"},{"name":"department","type":"string"},{"name":"shipTo","type":"COMPLEX&lt;json&gt;"},{"name":"details","type":"COMPLEX&lt;json&gt;"}]'</span>
     )
   )
 )
 PARTITIONED <span class="hljs-keyword">BY</span> <span class="hljs-keyword">ALL</span>
 </code></pre>
 <h2><a class="anchor" aria-hidden="true" id="ingest-a-json-string-as-complexjson"></a><a href="#ingest-a-json-string-as-complexjson" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Ingest a JSON string as COMPLEX&lt;json&gt;</h2>
 <p>If your source data contains serialized JSON strings, you can ingest the data as <code>COMPLEX&lt;JSON&gt;</code> as follows:</p>
 <ul>
 <li>During native batch ingestion, call the <code>parse_json</code> function in a <code>transform</code> object in the <code>transformSpec</code>.</li>
 <li>During SQL-based ingestion, use the PARSE_JSON keyword within your SELECT statement to transform the string values to JSON.</li>
 <li>If you are concerned that your data may not contain valid JSON, you can use <code>try_parse_json</code> for native batch or <code>TRY_PARSE_JSON</code> for SQL-based ingestion. For cases where the column does not contain valid JSON, Druid inserts a null value.</li>
 </ul>
 <p>If you are using a text input format like <code>tsv</code>, you need to use this method to ingest data into a <code>COMPLEX&lt;json&gt;</code> column.</p>
 <p>For example, consider the following deserialized row of the sample data set:</p>
 <pre><code class="hljs">{"time": "2022-06-13T10:10:35Z", "product": "Bike", "department":"Sports", "shipTo":"{<span class="hljs-symbol">\"</span>firstName<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>Henry<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>lastName<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>Wuckert<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>address<span class="hljs-symbol">\"</span>: {<span class="hljs-symbol">\"</span>street<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>5643 Jan Walk<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>city<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>Lake Bridget<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>state<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>HI<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>country<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>ME<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>postalCode<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>70204-2939<span class="hljs-symbol">\"</span>},<span class="hljs-symbol">\"</span>phoneNumbers<span class="hljs-symbol">\"</span>: [{<span class="hljs-symbol">\"</span>type<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>primary<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>number<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>593.475.0449 x86733<span class="hljs-symbol">\"</span> },{<span class="hljs-symbol">\"</span>type<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>secondary<span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\"</span>number<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>638-372-1210<span class="hljs-symbol">\"</span>}]}", "details":"{<span class="hljs-symbol">\"</span>color<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>ivory<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>price<span class="hljs-symbol">\"</span>:955.00}"}
 </code></pre>
 <p>The following examples demonstrate how to ingest the <code>shipTo</code> and <code>details</code> columns both as string type and as <code>COMPLEX&lt;json&gt;</code> in the <code>shipTo_parsed</code> and <code>details_parsed</code> columns.</p>
 <div class="tabs"><div class="nav-tabs"><div id="tab-group-34-tab-35" class="nav-link active" data-group="group_34" data-tab="tab-group-34-content-35">SQL</div><div id="tab-group-34-tab-36" class="nav-link" data-group="group_34" data-tab="tab-group-34-content-36">Native batch</div></div><div class="tab-content"><div id="tab-group-34-content-35" class="tab-pane active" data-group="group_34" tabindex="-1"><div><span><pre><code class="hljs">REPLACE INTO deserialized_example OVERWRITE ALL<br />WITH source AS (SELECT * FROM TABLE(<br />  EXTERN(<br />    '{"type":"inline","data":"{<span class="hljs-symbol">\"</span>time<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>2022-06-13T10:10:35Z<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>product<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>Bike<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>department<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>Sports<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>shipTo<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>firstName<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>Henry<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>lastName<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>Wuckert<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>address<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: {<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>street<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>5643 Jan Walk<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>city<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>Lake Bridget<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>state<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>HI<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>country<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>ME<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>postalCode<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>70204-2939<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>},<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>phoneNumbers<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: [{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>type<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>primary<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>number<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>593.475.0449 x86733<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span> },{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>type<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>secondary<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>number<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>638-372-1210<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>}]}<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>details<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>color<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>ivory<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>price<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:955.00}<span class="hljs-symbol">\"</span>}<span class="hljs-symbol">\n</span>"}',<br />    '{"type":"json"}',<br />    '[{"name":"time","type":"string"},{"name":"product","type":"string"},{"name":"department","type":"string"},{"name":"shipTo","type":"string"},{"name":"details","type":"string"}]'<br />  )<br />))<br />SELECT<br />  TIME_PARSE("time") AS __time,<br />  "product",<br />  "department",<br />  "shipTo",<br />  "details",<br />  PARSE_JSON("shipTo") as "shipTo_parsed", <br />  PARSE_JSON("details") as "details_parsed"<br />FROM source<br />PARTITIONED BY DAY<br /></code></pre>
 </span></div></div><div id="tab-group-34-content-36" class="tab-pane" data-group="group_34" tabindex="-1"><div><span><pre><code class="hljs">{<br />  "type": "index_parallel",<br />  "spec": {<br />    "ioConfig": {<br />      "type": "index_parallel",<br />      "inputSource": {<br />        "type": "inline",<br />        "data": "{<span class="hljs-symbol">\"</span>time<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>2022-06-13T10:10:35Z<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>product<span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\"</span>Bike<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>department<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>Sports<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>shipTo<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>firstName<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>Henry<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>lastName<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>Wuckert<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>address<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: {<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>street<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>5643 Jan Walk<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>city<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>Lake Bridget<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>state<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>HI<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>country<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>ME<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>postalCode<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>70204-2939<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>},<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>phoneNumbers<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>: [{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>type<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>primary<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>number<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>593.475.0449 x86733<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span> },{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>type<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>secondary<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>,<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>number<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>638-372-1210<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>}]}<span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\"</span>details<span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\"</span>{<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>color<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>ivory<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>, <span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>price<span class="hljs-symbol">\\</span><span class="hljs-symbol">\"</span>:955.00}<span class="hljs-symbol">\"</span>}<span class="hljs-symbol">\n</span>"<br />      },<br />      "inputFormat": {<br />        "type": "json"<br />      }<br />    },<br />    "tuningConfig": {<br />      "type": "index_parallel",<br />      "partitionsSpec": {<br />        "type": "dynamic"<br />      }<br />    },<br />    "dataSchema": {<br />      "dataSource": "deserialized_example",<br />      "timestampSpec": {<br />        "column": "time",<br />        "format": "iso"<br />      },<br />      "transformSpec": {<br />        "transforms": [<br />          {<br />            "type": "expression",<br />            "name": "shipTo_parsed",<br />            "expression": "parse_json(shipTo)"<br />          },<br />          {<br />            "type": "expression",<br />            "name": "details_parsed",<br />            "expression": "parse_json(details)"<br />          }<br />        ]<br />      },<br />      "dimensionsSpec": {<br />        "dimensions": [<br />          "product",<br />          "department",<br />          "shipTo",<br />          "details",<br />          "shipTo_parsed",<br />          "details_parsed"<br />        ]<br />      },<br />      "granularitySpec": {<br />        "queryGranularity": "none",<br />        "rollup": false,<br />        "segmentGranularity": "day"<br />      }<br />    }<br />  }<br />}<br /></code></pre>
 </span></div></div></div></div>
 <h2><a class="anchor" aria-hidden="true" id="querying-nested-columns"></a><a href="#querying-nested-columns" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Querying nested columns</h2>
 <p>Once ingested, Druid stores the JSON-typed columns as native JSON objects and presents them as <code>COMPLEX&lt;json&gt;</code>.</p>
 <p>See the <a href="/docs/26.0.0/querying/sql-json-functions.html">Nested columns functions reference</a> for information on the functions in the examples below.</p>
 <p>Druid supports a small, simplified subset of the <a href="https://github.com/json-path/JsonPath/blob/master/README.md">JSONPath syntax</a> operators, primarily limited to extracting individual values from nested data structures. See the <a href="/docs/26.0.0/querying/sql-json-functions.html#jsonpath-syntax">SQL JSON functions</a> page for details.</p>
 <h3><a class="anchor" aria-hidden="true" id="displaying-data-types"></a><a href="#displaying-data-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Displaying data types</h3>
 <p>The following example illustrates how you can display the data types for your columns. Note that <code>details</code> and <code>shipTo</code> display as <code>COMPLEX&lt;json&gt;</code>.</p>
 <h4><a class="anchor" aria-hidden="true" id="example-query-display-data-types"></a><a href="#example-query-display-data-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Display data types</h4>
 <p><img src="../assets/nested-display-data-types.png" alt="Display data types"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> TABLE_NAME, COLUMN_NAME, DATA_TYPE
 <span class="hljs-keyword">FROM</span> INFORMATION_SCHEMA.COLUMNS
 <span class="hljs-keyword">WHERE</span> TABLE_NAME = <span class="hljs-string">'nested_data_example'</span>
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"TABLE_NAME"</span>,<span class="hljs-string">"COLUMN_NAME"</span>,<span class="hljs-string">"DATA_TYPE"</span>],[<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>],[<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"nested_data_example"</span>,<span class="hljs-string">"__time"</span>,<span class="hljs-string">"TIMESTAMP"</span>],[<span class="hljs-string">"nested_data_example"</span>,<span class="hljs-string">"department"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"nested_data_example"</span>,<span class="hljs-string">"details"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>],[<span class="hljs-string">"nested_data_example"</span>,<span class="hljs-string">"product"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"nested_data_example"</span>,<span class="hljs-string">"shipTo"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="retrieving-json-data"></a><a href="#retrieving-json-data" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Retrieving JSON data</h3>
 <p>You can retrieve JSON data directly from a table. Druid returns the results as a JSON object, so you can't use grouping, aggregation, or filtering operators.</p>
 <h4><a class="anchor" aria-hidden="true" id="example-query-retrieve-json-data"></a><a href="#example-query-retrieve-json-data" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Retrieve JSON data</h4>
 <p>The following example query extracts all data from <code>nested_data_example</code>:</p>
 <p><img src="../assets/nested-retrieve-json.png" alt="Retrieve JSON data"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> nested_data_example
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"__time"</span>,<span class="hljs-string">"department"</span>,<span class="hljs-string">"details"</span>,<span class="hljs-string">"product"</span>,<span class="hljs-string">"shipTo"</span>],[<span class="hljs-string">"LONG"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>],[<span class="hljs-string">"TIMESTAMP"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"OTHER"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"OTHER"</span>],[<span class="hljs-string">"2022-06-13T07:52:29.000Z"</span>,<span class="hljs-string">"Sports"</span>,<span class="hljs-string">"{\"color\":\"sky blue\",\"price\":542.0}"</span>,<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"{\"firstName\":\"Russ\",\"lastName\":\"Cole\",\"address\":{\"street\":\"77173 Rusty Station\",\"city\":\"South Yeseniabury\",\"state\":\"WA\",\"country\":\"BL\",\"postalCode\":\"01893\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]}"</span>],[<span class="hljs-string">"2022-06-13T10:10:35.000Z"</span>,<span class="hljs-string">"Sports"</span>,<span class="hljs-string">"{\"color\":\"ivory\",\"price\":955.0}"</span>,<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"{\"firstName\":\"Henry\",\"lastName\":\"Wuckert\",\"address\":{\"street\":\"5643 Jan Walk\",\"city\":\"Lake Bridget\",\"state\":\"HI\",\"country\":\"ME\",\"postalCode\":\"70204-2939\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]}"</span>],[<span class="hljs-string">"2022-06-13T13:57:38.000Z"</span>,<span class="hljs-string">"Grocery"</span>,<span class="hljs-string">"{\"price\":8.0}"</span>,<span class="hljs-string">"Sausages"</span>,<span class="hljs-string">"{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}"</span>],[<span class="hljs-string">"2022-06-13T21:37:06.000Z"</span>,<span class="hljs-string">"Computers"</span>,<span class="hljs-string">"{\"color\":\"olive\",\"price\":90.0}"</span>,<span class="hljs-string">"Mouse"</span>,<span class="hljs-string">"{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}"</span>],[<span class="hljs-string">"2022-06-14T10:32:08.000Z"</span>,<span class="hljs-string">"Computers"</span>,<span class="hljs-string">"{\"color\":\"plum\",\"price\":40.0}"</span>,<span class="hljs-string">"Keyboard"</span>,<span class="hljs-string">"{\"firstName\":\"Sandra\",\"lastName\":\"Beatty\",\"address\":{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]}"</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="extracting-nested-data-elements"></a><a href="#extracting-nested-data-elements" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Extracting nested data elements</h3>
 <p>The <code>JSON_VALUE</code> function is specially optimized to provide native Druid level performance when processing nested literal values, as if they were flattened, traditional, Druid column types. It does this by reading from the specialized nested columns and indexes that are built and stored in JSON objects when Druid creates segments.</p>
 <p>Some operations using <code>JSON_VALUE</code> run faster than those using native Druid columns. For example, filtering numeric types uses the indexes built for nested numeric columns, which are not available for Druid DOUBLE, FLOAT, or LONG columns.</p>
 <p><code>JSON_VALUE</code> only returns literal types. Any paths that reference JSON objects or array types return null.</p>
 <blockquote>
 <p>To achieve the best possible performance, use the <code>JSON_VALUE</code> function whenever you query JSON objects.</p>
 </blockquote>
 <h4><a class="anchor" aria-hidden="true" id="example-query-extract-nested-data-elements"></a><a href="#example-query-extract-nested-data-elements" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Extract nested data elements</h4>
 <p>The following example query illustrates how to use <code>JSON_VALUE</code> to extract specified elements from a <code>COMPLEX&lt;json&gt;</code> object. Note that the returned values default to type VARCHAR.</p>
 <p><img src="../assets/nested-extract-elements.png" alt="Extract nested data elements"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   product,
   department,
   JSON_VALUE(shipTo, <span class="hljs-string">'$.address.country'</span>) <span class="hljs-keyword">as</span> country,
   JSON_VALUE(shipTo, <span class="hljs-string">'$.phoneNumbers[0].number'</span>) <span class="hljs-keyword">as</span> primaryPhone,
   JSON_VALUE(details, <span class="hljs-string">'$.price'</span>) <span class="hljs-keyword">as</span> price
 <span class="hljs-keyword">FROM</span> nested_data_example
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"product"</span>,<span class="hljs-string">"department"</span>,<span class="hljs-string">"country"</span>,<span class="hljs-string">"primaryPhone"</span>,<span class="hljs-string">"price"</span>],[<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>],[<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"Sports"</span>,<span class="hljs-string">"BL"</span>,<span class="hljs-string">"891-374-6188 x74568"</span>,<span class="hljs-string">"542.0"</span>],[<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"Sports"</span>,<span class="hljs-string">"ME"</span>,<span class="hljs-string">"593.475.0449 x86733"</span>,<span class="hljs-string">"955.0"</span>],[<span class="hljs-string">"Sausages"</span>,<span class="hljs-string">"Grocery"</span>,<span class="hljs-string">"AD"</span>,<span class="hljs-string">"(904) 890-0696 x581"</span>,<span class="hljs-string">"8.0"</span>],[<span class="hljs-string">"Mouse"</span>,<span class="hljs-string">"Computers"</span>,<span class="hljs-string">"CW"</span>,<span class="hljs-string">"(689) 766-4272 x60778"</span>,<span class="hljs-string">"90.0"</span>],[<span class="hljs-string">"Keyboard"</span>,<span class="hljs-string">"Computers"</span>,<span class="hljs-string">"TV"</span>,<span class="hljs-string">"1-788-771-7028 x8627"</span>,<span class="hljs-string">"40.0"</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="extracting-nested-data-elements-as-a-suggested-type"></a><a href="#extracting-nested-data-elements-as-a-suggested-type" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Extracting nested data elements as a suggested type</h3>
 <p>You can use the <code>RETURNING</code> keyword to provide type hints to the <code>JSON_VALUE</code> function. This way the SQL planner produces the correct native Druid query, leading to expected results. This keyword allows you to specify a SQL type for the <code>path</code> value.</p>
 <h4><a class="anchor" aria-hidden="true" id="example-query-extract-nested-data-elements-as-suggested-types"></a><a href="#example-query-extract-nested-data-elements-as-suggested-types" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Extract nested data elements as suggested types</h4>
 <p>The following example query illustrates how to use <code>JSON_VALUE</code> and the <code>RETURNING</code> keyword to extract an element of nested data and return it as specified types.</p>
 <p><img src="../assets/nested-extract-as-type.png" alt="Extract nested data elements as a suggested type"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   product,
   department,
   JSON_VALUE(shipTo, <span class="hljs-string">'$.address.country'</span>) <span class="hljs-keyword">as</span> country,
   JSON_VALUE(details, <span class="hljs-string">'$.price'</span> <span class="hljs-keyword">RETURNING</span> <span class="hljs-built_in">BIGINT</span>) <span class="hljs-keyword">as</span> price_int,
   JSON_VALUE(details, <span class="hljs-string">'$.price'</span> <span class="hljs-keyword">RETURNING</span> <span class="hljs-built_in">DECIMAL</span>) <span class="hljs-keyword">as</span> price_decimal,
   JSON_VALUE(details, <span class="hljs-string">'$.price'</span> <span class="hljs-keyword">RETURNING</span> <span class="hljs-built_in">VARCHAR</span>) <span class="hljs-keyword">as</span> price_varchar
 <span class="hljs-keyword">FROM</span> nested_data_example
 </code></pre>
 <p>Query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"product"</span>,<span class="hljs-string">"department"</span>,<span class="hljs-string">"country"</span>,<span class="hljs-string">"price_int"</span>,<span class="hljs-string">"price_decimal"</span>,<span class="hljs-string">"price_varchar"</span>],[<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"LONG"</span>,<span class="hljs-string">"DOUBLE"</span>,<span class="hljs-string">"STRING"</span>],[<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"BIGINT"</span>,<span class="hljs-string">"DECIMAL"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"Sports"</span>,<span class="hljs-string">"BL"</span>,<span class="hljs-number">542</span>,<span class="hljs-number">542.0</span>,<span class="hljs-string">"542.0"</span>],[<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"Sports"</span>,<span class="hljs-string">"ME"</span>,<span class="hljs-number">955</span>,<span class="hljs-number">955.0</span>,<span class="hljs-string">"955.0"</span>],[<span class="hljs-string">"Sausages"</span>,<span class="hljs-string">"Grocery"</span>,<span class="hljs-string">"AD"</span>,<span class="hljs-number">8</span>,<span class="hljs-number">8.0</span>,<span class="hljs-string">"8.0"</span>],[<span class="hljs-string">"Mouse"</span>,<span class="hljs-string">"Computers"</span>,<span class="hljs-string">"CW"</span>,<span class="hljs-number">90</span>,<span class="hljs-number">90.0</span>,<span class="hljs-string">"90.0"</span>],[<span class="hljs-string">"Keyboard"</span>,<span class="hljs-string">"Computers"</span>,<span class="hljs-string">"TV"</span>,<span class="hljs-number">40</span>,<span class="hljs-number">40.0</span>,<span class="hljs-string">"40.0"</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="grouping-aggregating-and-filtering"></a><a href="#grouping-aggregating-and-filtering" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Grouping, aggregating, and filtering</h3>
 <p>You can use <code>JSON_VALUE</code> expressions in any context where you can use traditional Druid columns, such as grouping, aggregation, and filtering.</p>
 <h4><a class="anchor" aria-hidden="true" id="example-query-grouping-and-filtering"></a><a href="#example-query-grouping-and-filtering" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Grouping and filtering</h4>
 <p>The following example query illustrates how to use SUM, WHERE, GROUP BY, and ORDER BY operators with <code>JSON_VALUE</code>.</p>
 <p><img src="../assets/nested-group-aggregate.png" alt="Group, aggregate, filter"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   product,
   JSON_VALUE(shipTo, <span class="hljs-string">'$.address.country'</span>),
   <span class="hljs-keyword">SUM</span>(JSON_VALUE(details, <span class="hljs-string">'$.price'</span> <span class="hljs-keyword">RETURNING</span> <span class="hljs-built_in">BIGINT</span>))
 <span class="hljs-keyword">FROM</span> nested_data_example
 <span class="hljs-keyword">WHERE</span> JSON_VALUE(shipTo, <span class="hljs-string">'$.address.country'</span>) <span class="hljs-keyword">in</span> (<span class="hljs-string">'BL'</span>, <span class="hljs-string">'CW'</span>)
 <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> <span class="hljs-number">1</span>,<span class="hljs-number">2</span>
 <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> <span class="hljs-number">3</span> <span class="hljs-keyword">DESC</span>
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"product"</span>,<span class="hljs-string">"EXPR$1"</span>,<span class="hljs-string">"EXPR$2"</span>],[<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"LONG"</span>],[<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"BIGINT"</span>],[<span class="hljs-string">"Bike"</span>,<span class="hljs-string">"BL"</span>,<span class="hljs-number">542</span>],[<span class="hljs-string">"Mouse"</span>,<span class="hljs-string">"CW"</span>,<span class="hljs-number">90</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="transforming-json-object-data"></a><a href="#transforming-json-object-data" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Transforming JSON object data</h3>
 <p>In addition to <code>JSON_VALUE</code>, Druid offers a number of operators that focus on transforming JSON object data:</p>
 <ul>
 <li><code>JSON_QUERY</code></li>
 <li><code>JSON_OBJECT</code></li>
 <li><code>PARSE_JSON</code></li>
 <li><code>TO_JSON_STRING</code></li>
 </ul>
 <p>These functions are primarily intended for use with SQL-based ingestion to transform data during insert operations, but they also work in traditional Druid SQL queries. Because most of these functions output JSON objects, they have the same limitations when used in traditional Druid queries as interacting with the JSON objects directly.</p>
 <h4><a class="anchor" aria-hidden="true" id="example-query-return-results-in-a-json-object"></a><a href="#example-query-return-results-in-a-json-object" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Return results in a JSON object</h4>
 <p>You can use the <code>JSON_QUERY</code> function to extract a partial structure from any JSON input and return results in a JSON object. Unlike <code>JSON_VALUE</code> it can extract objects and arrays.</p>
 <p>The following example query illustrates the differences in output between <code>JSON_VALUE</code> and <code>JSON_QUERY</code>. The two output columns for <code>JSON_VALUE</code> contain null values only because <code>JSON_VALUE</code> only returns literal types.</p>
 <p><img src="../assets/nested-return-json.png" alt="Return results in a JSON object"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   JSON_VALUE(shipTo, <span class="hljs-string">'$.address'</span>),
   JSON_QUERY(shipTo, <span class="hljs-string">'$.address'</span>),
   JSON_VALUE(shipTo, <span class="hljs-string">'$.phoneNumbers'</span>),
   JSON_QUERY(shipTo, <span class="hljs-string">'$.phoneNumbers'</span>)
 <span class="hljs-keyword">FROM</span> nested_data_example
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"EXPR$0"</span>,<span class="hljs-string">"EXPR$1"</span>,<span class="hljs-string">"EXPR$2"</span>,<span class="hljs-string">"EXPR$3"</span>],[<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>],[<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"OTHER"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"OTHER"</span>],[<span class="hljs-string">""</span>,<span class="hljs-string">"{\"street\":\"77173 Rusty Station\",\"city\":\"South Yeseniabury\",\"state\":\"WA\",\"country\":\"BL\",\"postalCode\":\"01893\"}"</span>,<span class="hljs-string">""</span>,<span class="hljs-string">"[{\"type\":\"primary\",\"number\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]"</span>],[<span class="hljs-string">""</span>,<span class="hljs-string">"{\"street\":\"5643 Jan Walk\",\"city\":\"Lake Bridget\",\"state\":\"HI\",\"country\":\"ME\",\"postalCode\":\"70204-2939\"}"</span>,<span class="hljs-string">""</span>,<span class="hljs-string">"[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]"</span>],[<span class="hljs-string">""</span>,<span class="hljs-string">"{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"}"</span>,<span class="hljs-string">""</span>,<span class="hljs-string">"[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]"</span>],[<span class="hljs-string">""</span>,<span class="hljs-string">"{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"}"</span>,<span class="hljs-string">""</span>,<span class="hljs-string">"[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]"</span>],[<span class="hljs-string">""</span>,<span class="hljs-string">"{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"}"</span>,<span class="hljs-string">""</span>,<span class="hljs-string">"[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]"</span>]]
 </code></pre>
 <h4><a class="anchor" aria-hidden="true" id="example-query-combine-multiple-json-inputs-into-a-single-json-object-value"></a><a href="#example-query-combine-multiple-json-inputs-into-a-single-json-object-value" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Combine multiple JSON inputs into a single JSON object value</h4>
 <p>The following query illustrates how to use <code>JSON_OBJECT</code> to combine nested data elements into a new object.</p>
 <p><img src="../assets/nested-combined-json.png" alt="Combine JSON inputs"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   JSON_OBJECT(<span class="hljs-keyword">KEY</span> <span class="hljs-string">'shipTo'</span> <span class="hljs-keyword">VALUE</span> JSON_QUERY(shipTo, <span class="hljs-string">'$'</span>), <span class="hljs-keyword">KEY</span> <span class="hljs-string">'details'</span> <span class="hljs-keyword">VALUE</span> JSON_QUERY(details, <span class="hljs-string">'$'</span>)) <span class="hljs-keyword">as</span> combinedJson
 <span class="hljs-keyword">FROM</span> nested_data_example
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"combinedJson"</span>],[<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>],[<span class="hljs-string">"OTHER"</span>],[<span class="hljs-string">"{\"details\":{\"color\":\"sky blue\",\"price\":542.0},\"shipTo\":{\"firstName\":\"Russ\",\"lastName\":\"Cole\",\"address\":{\"street\":\"77173 Rusty Station\",\"city\":\"South Yeseniabury\",\"state\":\"WA\",\"country\":\"BL\",\"postalCode\":\"01893\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"891-374-6188 x74568\"},{\"type\":\"secondary\",\"number\":\"1-248-998-4426 x33037\"}]}}"</span>],[<span class="hljs-string">"{\"details\":{\"color\":\"ivory\",\"price\":955.0},\"shipTo\":{\"firstName\":\"Henry\",\"lastName\":\"Wuckert\",\"address\":{\"street\":\"5643 Jan Walk\",\"city\":\"Lake Bridget\",\"state\":\"HI\",\"country\":\"ME\",\"postalCode\":\"70204-2939\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"593.475.0449 x86733\"},{\"type\":\"secondary\",\"number\":\"638-372-1210\"}]}}"</span>],[<span class="hljs-string">"{\"details\":{\"price\":8.0},\"shipTo\":{\"firstName\":\"Forrest\",\"lastName\":\"Brekke\",\"address\":{\"street\":\"41548 Collier Divide\",\"city\":\"Wintheiserborough\",\"state\":\"WA\",\"country\":\"AD\",\"postalCode\":\"27577-6784\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(904) 890-0696 x581\"},{\"type\":\"secondary\",\"number\":\"676.895.6759\"}]}}"</span>],[<span class="hljs-string">"{\"details\":{\"color\":\"olive\",\"price\":90.0},\"shipTo\":{\"firstName\":\"Rickey\",\"lastName\":\"Rempel\",\"address\":{\"street\":\"6232 Green Glens\",\"city\":\"New Fermin\",\"state\":\"HI\",\"country\":\"CW\",\"postalCode\":\"98912-1195\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"(689) 766-4272 x60778\"},{\"type\":\"secondary\",\"number\":\"375.662.4737 x24707\"}]}}"</span>],[<span class="hljs-string">"{\"details\":{\"color\":\"plum\",\"price\":40.0},\"shipTo\":{\"firstName\":\"Sandra\",\"lastName\":\"Beatty\",\"address\":{\"street\":\"293 Grant Well\",\"city\":\"Loischester\",\"state\":\"FL\",\"country\":\"TV\",\"postalCode\":\"88845-0066\"},\"phoneNumbers\":[{\"type\":\"primary\",\"number\":\"1-788-771-7028 x8627\"},{\"type\":\"secondary\",\"number\":\"1-460-496-4884 x887\"}]}}"</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="using-other-transform-functions"></a><a href="#using-other-transform-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Using other transform functions</h3>
 <p>Druid provides the following additional transform functions:</p>
 <ul>
 <li><code>PARSE_JSON</code>: Deserializes a string value into a JSON object.</li>
 <li><code>TO_JSON_STRING</code>: Performs the operation of <code>TO_JSON</code> and then serializes the value into a string.</li>
 </ul>
 <h4><a class="anchor" aria-hidden="true" id="example-query-parse-and-deserialize-data"></a><a href="#example-query-parse-and-deserialize-data" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Parse and deserialize data</h4>
 <p>The following query illustrates how to use the transform functions to parse and deserialize data.</p>
 <p><img src="../assets/nested-parse-deserialize.png" alt="Parse and deserialize data"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   PARSE_JSON(<span class="hljs-string">'{"x":"y"}'</span>),
   TO_JSON_STRING(<span class="hljs-string">'{"x":"y"}'</span>),
   TO_JSON_STRING(PARSE_JSON(<span class="hljs-string">'{"x":"y"}'</span>))
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"EXPR$0"</span>,<span class="hljs-string">"EXPR$2"</span>,<span class="hljs-string">"EXPR$3"</span>],[<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>],[<span class="hljs-string">"OTHER"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"{\"x\":\"y\"}"</span>,<span class="hljs-string">"\"{\\\"x\\\":\\\"y\\\"}\""</span>,<span class="hljs-string">"{\"x\":\"y\"}"</span>]]
 </code></pre>
 <h3><a class="anchor" aria-hidden="true" id="using-helper-operators"></a><a href="#using-helper-operators" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Using helper operators</h3>
 <p>The <code>JSON_KEYS</code> and <code>JSON_PATHS</code> functions are helper operators that you can use to examine JSON object schema. Use them to plan your queries, for example to work out which paths to use in <code>JSON_VALUE</code>.</p>
 <h4><a class="anchor" aria-hidden="true" id="example-query-examine-json-object-schema"></a><a href="#example-query-examine-json-object-schema" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Example query: Examine JSON object schema</h4>
 <p>The following query illustrates how to use the helper operators to examine a nested data object.</p>
 <p><img src="../assets/nested-examine-schema.png" alt="Examine JSON object schema"></p>
 <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span>
   ARRAY_CONCAT_AGG(<span class="hljs-keyword">DISTINCT</span> JSON_KEYS(shipTo, <span class="hljs-string">'$.'</span>)),
   ARRAY_CONCAT_AGG(<span class="hljs-keyword">DISTINCT</span> JSON_KEYS(shipTo, <span class="hljs-string">'$.address'</span>)),
   ARRAY_CONCAT_AGG(<span class="hljs-keyword">DISTINCT</span> JSON_PATHS(shipTo))
 <span class="hljs-keyword">FROM</span> nested_data_example
 </code></pre>
 <p>Example query results:</p>
 <pre><code class="hljs css language-json">[[<span class="hljs-string">"EXPR$0"</span>,<span class="hljs-string">"EXPR$1"</span>,<span class="hljs-string">"EXPR$2"</span>,<span class="hljs-string">"EXPR$3"</span>],[<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>,<span class="hljs-string">"COMPLEX&lt;json&gt;"</span>,<span class="hljs-string">"STRING"</span>,<span class="hljs-string">"STRING"</span>],[<span class="hljs-string">"OTHER"</span>,<span class="hljs-string">"OTHER"</span>,<span class="hljs-string">"VARCHAR"</span>,<span class="hljs-string">"VARCHAR"</span>],[<span class="hljs-string">"{\"x\":\"y\"}"</span>,<span class="hljs-string">"\"{\\\"x\\\":\\\"y\\\"}\""</span>,<span class="hljs-string">"\"{\\\"x\\\":\\\"y\\\"}\""</span>,<span class="hljs-string">"{\"x\":\"y\"}"</span>]]
 </code></pre>
 <h2><a class="anchor" aria-hidden="true" id="known-issues"></a><a href="#known-issues" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Known issues</h2>
 <p>Before you start using the nested columns feature, consider the following known issues:</p>
 <ul>
 <li>Directly using <code>COMPLEX&lt;json&gt;</code> columns and expressions is not well integrated into the Druid query engine. It can result in errors or undefined behavior when grouping and filtering, and when you use <code>COMPLEX&lt;json&gt;</code> objects as inputs to aggregators. As a workaround, consider using <code>TO_JSON_STRING</code> to coerce the values to strings before you perform these operations.</li>
 <li>Directly using array-typed outputs from <code>JSON_KEYS</code> and <code>JSON_PATHS</code> is moderately supported by the Druid query engine. You can group on these outputs, and there are a number of array expressions that can operate on these values, such as <code>ARRAY_CONCAT_AGG</code>. However, some operations are not well defined for use outside array-specific functions, such as filtering using <code>=</code> or <code>IS NULL</code>.</li>
 <li>Input validation for JSON SQL operators is currently incomplete, which sometimes results in undefined behavior or unhelpful error messages.</li>
 <li>Ingesting data with a very complex nested structure is potentially an expensive operation and may require you to tune ingestion tasks and/or cluster parameters to account for increased memory usage or overall task run time. When you tune your ingestion configuration, treat each nested literal field inside an object as a flattened top-level Druid column.</li>
 </ul>
 <h2><a class="anchor" aria-hidden="true" id="further-reading"></a><a href="#further-reading" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Further reading</h2>
 <p>For more information, see the following pages:</p>
 <ul>
 <li><a href="/docs/26.0.0/querying/sql-json-functions.html">Nested columns functions reference</a> for details of the functions used in the examples on this page.</li>
 <li><a href="/docs/26.0.0/multi-stage-query/index.html">Multi-stage query architecture overview</a> for information on how to set up and use this feature.</li>
 <li><a href="/docs/26.0.0/ingestion/ingestion-spec.html">Ingestion spec reference</a> for information on native ingestion and <a href="/docs/26.0.0/ingestion/ingestion-spec.html#transformspec"><code>transformSpec</code></a>.</li>
 <li><a href="/docs/26.0.0/ingestion/data-formats.html">Data formats</a> for information on <a href="/docs/26.0.0/ingestion/data-formats.html#flattenspec"><code>flattenSpec</code></a>.</li>
 </ul>
 </span></div></article></div><div class="docs-prevnext"><a class="docs-prev button" href="/docs/26.0.0/querying/multi-value-dimensions.html"><span class="arrow-prev">← </span><span>Multi-value dimensions</span></a><a class="docs-next button" href="/docs/26.0.0/querying/multitenancy.html"><span>Multitenancy</span><span class="arrow-next"> →</span></a></div></div></div><nav class="onPageNav"><ul class="toc-headings"><li><a href="#example-nested-data">Example nested data</a></li><li><a href="#native-batch-ingestion">Native batch ingestion</a><ul class="toc-headings"><li><a href="#transform-data-during-batch-ingestion">Transform data during batch ingestion</a></li></ul></li><li><a href="#sql-based-ingestion">SQL-based ingestion</a></li><li><a href="#streaming-ingestion">Streaming ingestion</a><ul class="toc-headings"><li><a href="#transform-data-during-sql-based-ingestion">Transform data during SQL-based ingestion</a></li></ul></li><li><a href="#ingest-a-json-string-as-complexjson">Ingest a JSON string as COMPLEX&lt;json&gt;</a></li><li><a href="#querying-nested-columns">Querying nested columns</a><ul class="toc-headings"><li><a href="#displaying-data-types">Displaying data types</a></li><li><a href="#retrieving-json-data">Retrieving JSON data</a></li><li><a href="#extracting-nested-data-elements">Extracting nested data elements</a></li><li><a href="#extracting-nested-data-elements-as-a-suggested-type">Extracting nested data elements as a suggested type</a></li><li><a href="#grouping-aggregating-and-filtering">Grouping, aggregating, and filtering</a></li><li><a href="#transforming-json-object-data">Transforming JSON object data</a></li><li><a href="#using-other-transform-functions">Using other transform functions</a></li><li><a href="#using-helper-operators">Using helper operators</a></li></ul></li><li><a href="#known-issues">Known issues</a></li><li><a href="#further-reading">Further reading</a></li></ul></nav></div><footer class="nav-footer druid-footer" id="footer"><div class="container"><div class="text-center"><p><a href="/technology">Technology</a> · <a href="/use-cases">Use Cases</a> · <a href="/druid-powered">Powered by Druid</a> · <a href="/docs/26.0.0/">Docs</a> · <a href="/community/">Community</a> · <a href="/downloads.html">Download</a> · <a href="/faq">FAQ</a></p></div><div class="text-center"><a title="Join the user group" href="https://groups.google.com/forum/#!forum/druid-user" target="_blank"><span class="fa fa-comments"></span></a> · <a title="Follow Druid" href="https://twitter.com/druidio" target="_blank"><span class="fab fa-twitter"></span></a> · <a title="Download via Apache" href="https://www.apache.org/dyn/closer.cgi?path=/incubator/druid/{{ site.druid_versions[0].versions[0].version }}/apache-druid-{{ site.druid_versions[0].versions[0].version }}-bin.tar.gz" target="_blank"><span class="fas fa-feather"></span></a> · <a title="GitHub" href="https://github.com/apache/druid" target="_blank"><span class="fab fa-github"></span></a></div><div class="text-center license">Copyright © 2022 <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a>.<br/>Except where otherwise noted, licensed under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC BY-SA 4.0</a>.<br/>Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></footer></div><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.js"></script><script>
                 document.addEventListener('keyup', function(e) {
                   if (e.target !== document.body) {
                     return;
                   }
                   // keyCode for '/' (slash)
                   if (e.keyCode === 191) {
                     const search = document.getElementById('search_input_react');
                     search && search.focus();
                   }
                 });
               </script><script>
               var search = docsearch({
                 appId: 'CPK9PMSCEY',
                 apiKey: 'd4ef4ffe3a2f0c7d1e34b062fd98736b',
                 indexName: 'apache_druid',
                 inputSelector: '#search_input_react',
                 algoliaOptions: {"facetFilters":["language:en","version:26.0.0"]}
               });
             </script></body></html>