| <!DOCTYPE html><html lang="en"><head><meta charSet="utf-8"/><meta http-equiv="X-UA-Compatible" content="IE=edge"/><title>Unnest arrays within a column · Apache Druid</title><meta name="viewport" content="width=device-width, initial-scale=1.0"/><link rel="canonical" href="https://druid.apache.org/docs/latest/tutorials/tutorial-unnest-arrays.html"/><meta name="generator" content="Docusaurus"/><meta name="description" content="<!--"/><meta name="docsearch:language" content="en"/><meta name="docsearch:version" content="26.0.0" /><meta property="og:title" content="Unnest arrays within a column · Apache Druid"/><meta property="og:type" content="website"/><meta property="og:url" content="https://druid.apache.org/index.html"/><meta property="og:description" content="<!--"/><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/latest/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>Tutorials</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/latest/design/index.html">Introduction to Apache Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/index.html">Quickstart (local)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/single-server.html">Single server deployment</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/tutorials/tutorial-batch.html">Load files natively</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-msq-extern.html">Load files using SQL 🆕</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-kafka.html">Load from Apache Kafka</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-batch-hadoop.html">Load from Apache Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-query.html">Querying data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-rollup.html">Roll-up</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-sketches-theta.html">Theta sketches</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-retention.html">Configuring data retention</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-update-data.html">Updating existing data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-compaction.html">Compacting segments</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-delete-data.html">Deleting data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-ingestion-spec.html">Writing an ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-transform-spec.html">Transforming input data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/docker.html">Tutorial: Run with Docker</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-kerberos-hadoop.html">Kerberized HDFS deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-msq-convert-spec.html">Convert ingestion spec to SQL</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-sql-query-view.html">Get to know Query view</a></li><li class="navListItem navListItemActive"><a class="navItem" href="/docs/latest/tutorials/tutorial-unnest-arrays.html">Unnesting arrays</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-jupyter-index.html">Jupyter Notebook tutorials</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-jupyter-docker.html">Docker for tutorials</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/design/architecture.html">Design</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/segments.html">Segments</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/processes.html">Processes and servers</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/dependencies/deep-storage.html">Deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/dependencies/metadata-storage.html">Metadata storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/ingestion/index.html">Ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/data-formats.html">Data formats</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/data-model.html">Data model</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/rollup.html">Data rollup</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/partitioning.html">Partitioning</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/ingestion-spec.html">Ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/development/extensions-core/kafka-ingestion.html">Apache Kafka ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-supervisor-reference.html">Apache Kafka supervisor</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-supervisor-operations.html">Apache Kafka operations</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/ingestion/native-batch.html">Native batch</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-input-sources.html">Native batch: input sources</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/migrate-from-firehose.html">Migrate from firehose</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/multi-stage-query/index.html">Overview</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/concepts.html">Key concepts</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/api.html">API</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/security.html">Security</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/examples.html">Examples</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/reference.html">Reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/known-issues.html">Known issues</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/tasks.html">Task reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/data-management/index.html">Overview</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/update.html">Data updates</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/delete.html">Data deletion</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/schema-changes.html">Schema changes</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/compaction.html">Compaction</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/querying/sql.html">Overview and syntax</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-data-types.html">SQL data types</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-operators.html">Operators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-scalar.html">Scalar functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-aggregations.html">Aggregation functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-multivalue-string-functions.html">Multi-value string functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-json-functions.html">JSON functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-functions.html">All functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-api.html">Druid SQL API</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-jdbc.html">JDBC driver API</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-query-context.html">SQL query context</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-metadata-tables.html">SQL metadata tables</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-translation.html">SQL query translation</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/latest/querying/querying.html">Native queries</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/query-execution.html">Query execution</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/troubleshooting.html">Troubleshooting</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Concepts</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/querying/datasource.html">Datasources</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/joins.html">Joins</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/lookups.html">Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/multi-value-dimensions.html">Multi-value dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/nested-columns.html">Nested columns</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/multitenancy.html">Multitenancy</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/caching.html">Query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/using-caching.html">Using query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/querying/timeseriesquery.html">Timeseries</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/topnquery.html">TopN</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/groupbyquery.html">GroupBy</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/scan-query.html">Scan</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/searchquery.html">Search</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/timeboundaryquery.html">TimeBoundary</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/segmentmetadataquery.html">SegmentMetadata</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/querying/filters.html">Filters</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/granularities.html">Granularities</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/dimensionspecs.html">Dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/aggregations.html">Aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/post-aggregations.html">Post-aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/misc/math-expr.html">Expressions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/having.html">Having filters (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/limitspec.html">Sorting and limiting (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/topnmetricspec.html">Sorting (topN)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sorting-orders.html">String comparators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/virtual-columns.html">Virtual columns</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/configuration/index.html">Configuration reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions.html">Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/operations/web-console.html">Web console</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/operations/security-overview.html">Security overview</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/security-user-auth.html">User authentication and authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/auth-ldap.html">LDAP auth</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/password-provider.html">Password providers</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/dynamic-config-provider.html">Dynamic Config Providers</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/operations/basic-cluster-tuning.html">Basic cluster tuning</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/segment-optimization.html">Segment size optimization</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/mixed-workloads.html">Mixed workloads</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/http-compression.html">HTTP compression</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/operations/request-logging.html">Request logging</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/metrics.html">Metrics</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/alerts.html">Alerts</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/latest/operations/api-reference.html">API reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/high-availability.html">High availability</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/rolling-updates.html">Rolling updates</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/rule-configuration.html">Using rules to drop and retain data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/operations/dump-segment.html">dump-segment tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/reset-cluster.html">reset-cluster tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/insert-segment-to-db.html">insert-segment-to-db tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/pull-deps.html">pull-deps tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/deep-storage-migration.html">Deep storage migration</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/export-metadata.html">Export Metadata Tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/metadata-migration.html">Metadata Migration</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/development/overview.html">Developing on Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/modules.html">Creating extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/javascript.html">JavaScript functionality</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/build.html">Build from source</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/versioning.html">Versioning</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/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/latest/comparisons/druid-vs-elasticsearch.html">Apache Druid vs Elasticsearch</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/latest/comparisons/druid-vs-kudu.html">Apache Druid vs Kudu</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-redshift.html">Apache Druid vs Redshift</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-spark.html">Apache Druid vs Spark</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-sql-on-hadoop.html">Apache Druid vs SQL-on-Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/auth.html">Authentication and Authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/broker.html">Broker</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/coordinator.html">Coordinator Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/historical.html">Historical Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/indexer.html">Indexer Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/indexing-service.html">Indexing Service</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/middlemanager.html">MiddleManager Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/overlord.html">Overlord Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/router.html">Router Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/peons.html">Peons</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/approximate-histograms.html">Approximate Histogram aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/avro.html">Apache Avro</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/azure.html">Microsoft Azure</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/bloom-filter.html">Bloom Filter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-extension.html">DataSketches extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-hll.html">DataSketches HLL Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-quantiles.html">DataSketches Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-theta.html">DataSketches Theta Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-tuple.html">DataSketches Tuple Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-basic-security.html">Basic Security</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-kerberos.html">Kerberos</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-lookups.html">Cached Lookup Module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-ranger-security.html">Apache Ranger Security</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/google.html">Google Cloud Storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/hdfs.html">HDFS</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-extraction-namespace.html">Apache Kafka Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/lookups-cached-global.html">Globally Cached Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/mysql.html">MySQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/orc.html">ORC Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-pac4j.html">Druid pac4j based Security extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/parquet.html">Apache Parquet Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/postgresql.html">PostgreSQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/protobuf.html">Protobuf</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/s3.html">S3-compatible</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/simple-client-sslcontext.html">Simple SSLContext Provider Module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/stats.html">Stats aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/test-stats.html">Test Stats Aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-aws-rds.html">Druid AWS RDS Module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kubernetes.html">Kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/ambari-metrics-emitter.html">Ambari Metrics Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/cassandra.html">Apache Cassandra</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/cloudfiles.html">Rackspace Cloud Files</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/distinctcount.html">DistinctCount Aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/graphite.html">Graphite Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/influx.html">InfluxDB Line Protocol Parser</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/influxdb-emitter.html">InfluxDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/kafka-emitter.html">Kafka Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/materialized-view.html">Materialized View</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/momentsketch-quantiles.html">Moment Sketches for Approximate Quantiles module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/moving-average-query.html">Moving Average Query</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/opentsdb-emitter.html">OpenTSDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/redis-cache.html">Druid Redis Cache</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/sqlserver.html">Microsoft SQLServer</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/statsd.html">StatsD Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/tdigestsketch-quantiles.html">T-Digest Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/thrift.html">Thrift</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/time-min-max.html">Timestamp Min/Max aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/gce-extensions.html">GCE Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/aliyun-oss.html">Aliyun OSS</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/prometheus.html">Prometheus Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/kubernetes.html">kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/hll-old.html">Cardinality/HyperUnique aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/select-query.html">Select</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-firehose.html">Firehose (deprecated)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-simple-task.html">Native batch (simple)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/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/tutorials/tutorial-unnest-arrays.md" target="_blank" rel="noreferrer noopener">Edit</a><h1 id="__docusaurus" class="postHeaderTitle">Unnest arrays within a column</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. |
| --> |
| <blockquote> |
| <p>If you're looking for information about how to unnest <code>COMPLEX<json></code> columns, see <a href="/docs/latest/querying/nested-columns.html">Nested columns</a>.</p> |
| </blockquote> |
| <blockquote> |
| <p>The unnest datasource and UNNEST SQL function are <a href="/docs/latest/development/experimental.html">experimental</a>. Their API and behavior are subject |
| to change in future releases. It is not recommended to use this feature in production at this time.</p> |
| </blockquote> |
| <p>This tutorial demonstrates how to use the unnest datasource to unnest a column that has data stored in arrays. For example, if you have a column named <code>dim3</code> with values like <code>[a,b]</code> or <code>[c,d,f]</code>, the unnest datasource can output the data to a new column with individual rows that contain single values like <code>a</code> and <code>b</code>. When doing this, be mindful of the following:</p> |
| <ul> |
| <li>Unnesting data can dramatically increase the total number of rows.</li> |
| <li>You cannot unnest an array within an array.</li> |
| </ul> |
| <p>You can use the Druid console or API to unnest data. To start though, you may want to use the Druid console so that viewing the nested and unnested data is easier.</p> |
| <h2><a class="anchor" aria-hidden="true" id="prerequisites"></a><a href="#prerequisites" 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>Prerequisites</h2> |
| <p>You need a Druid cluster, such as the <a href="/docs/latest/tutorials/index.html">quickstart</a>. The cluster does not need any existing datasources. You'll load a basic one as part of this tutorial.</p> |
| <h2><a class="anchor" aria-hidden="true" id="load-data-with-nested-values"></a><a href="#load-data-with-nested-values" 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>Load data with nested values</h2> |
| <p>The data you're ingesting contains a handful of rows that resemble the following:</p> |
| <pre><code class="hljs"><span class="hljs-string">t:</span><span class="hljs-number">2000</span><span class="hljs-number">-01</span><span class="hljs-number">-01</span>, <span class="hljs-string">m1:</span><span class="hljs-number">1.0</span>, <span class="hljs-string">m2:</span><span class="hljs-number">1.0</span>, <span class="hljs-string">dim1:</span>, <span class="hljs-string">dim2:</span>[a], <span class="hljs-string">dim3:</span>[a,b], <span class="hljs-string">dim4:</span>[x,y], <span class="hljs-string">dim5:</span>[a,b] |
| </code></pre> |
| <p>The focus of this tutorial is on the nested array of values in <code>dim3</code>.</p> |
| <p>You can load this data by running a query for SQL-based ingestion or submitting a JSON-based ingestion spec. The example loads data into a table named <code>nested_data</code>:</p> |
| <div class="tabs"><div class="nav-tabs"><div id="tab-group-37-tab-38" class="nav-link active" data-group="group_37" data-tab="tab-group-37-content-38">SQL-based ingestion</div><div id="tab-group-37-tab-39" class="nav-link" data-group="group_37" data-tab="tab-group-37-content-39">Ingestion spec</div></div><div class="tab-content"><div id="tab-group-37-content-38" class="tab-pane active" data-group="group_37" tabindex="-1"><div><span><pre><code class="hljs css language-sql"><span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">INTO</span> nested_data OVERWRITE <span class="hljs-keyword">ALL</span><br /><span class="hljs-keyword">SELECT</span><br /> TIME_PARSE(<span class="hljs-string">"t"</span>) <span class="hljs-keyword">as</span> __time,<br /> dim1,<br /> dim2,<br /> dim3,<br /> dim4,<br /> dim5,<br /> m1,<br /> m2<br /><span class="hljs-keyword">FROM</span> <span class="hljs-keyword">TABLE</span>(<br /> EXTERN(<br /> <span class="hljs-string">'{"type":"inline","data":"{\"t\":\"2000-01-01\",\"m1\":\"1.0\",\"m2\":\"1.0\",\"dim1\":\"\",\"dim2\":[\"a\"],\"dim3\":[\"a\",\"b\"],\"dim4\":[\"x\",\"y\"],\"dim5\":[\"a\",\"b\"]},\n{\"t\":\"2000-01-02\",\"m1\":\"2.0\",\"m2\":\"2.0\",\"dim1\":\"10.1\",\"dim2\":[],\"dim3\":[\"c\",\"d\"],\"dim4\":[\"e\",\"f\"],\"dim5\":[\"a\",\"b\",\"c\",\"d\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-01\",\"m1\":\"4.0\",\"m2\":\"4.0\",\"dim1\":\"1\",\"dim2\":[\"a\"],\"dim3\":[\"g\",\"h\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"i\",\"j\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"m\",\"n\"]}"}'</span>,<br /> <span class="hljs-string">'{"type":"json"}'</span>,<br /> <span class="hljs-string">'[{"name":"t","type":"string"},{"name":"dim1","type":"string"},{"name":"dim2","type":"string"},{"name":"dim3","type":"string"},{"name":"dim4","type":"string"},{"name":"dim5","type":"string"},{"name":"m1","type":"float"},{"name":"m2","type":"double"}]'</span><br /> )<br />)<br />PARTITIONED <span class="hljs-keyword">BY</span> <span class="hljs-keyword">YEAR</span> <br /></code></pre> |
| </span></div></div><div id="tab-group-37-content-39" class="tab-pane" data-group="group_37" tabindex="-1"><div><span><pre><code class="hljs css language-json">{<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,<br /> <span class="hljs-attr">"spec"</span>: {<br /> <span class="hljs-attr">"ioConfig"</span>: {<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,<br /> <span class="hljs-attr">"inputSource"</span>: {<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"inline"</span>,<br /> <span class="hljs-attr">"data"</span>:<span class="hljs-string">"{\"t\":\"2000-01-01\",\"m1\":\"1.0\",\"m2\":\"1.0\",\"dim1\":\"\",\"dim2\":[\"a\"],\"dim3\":[\"a\",\"b\"],\"dim4\":[\"x\",\"y\"],\"dim5\":[\"a\",\"b\"]},\n{\"t\":\"2000-01-02\",\"m1\":\"2.0\",\"m2\":\"2.0\",\"dim1\":\"10.1\",\"dim2\":[],\"dim3\":[\"c\",\"d\"],\"dim4\":[\"e\",\"f\"],\"dim5\":[\"a\",\"b\",\"c\",\"d\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-01\",\"m1\":\"4.0\",\"m2\":\"4.0\",\"dim1\":\"1\",\"dim2\":[\"a\"],\"dim3\":[\"g\",\"h\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"i\",\"j\"]},\n{\"t\":\"2001-01-03\",\"m1\":\"6.0\",\"m2\":\"6.0\",\"dim1\":\"abc\",\"dim2\":[\"a\"],\"dim3\":[\"k\",\"l\"]},\n{\"t\":\"2001-01-02\",\"m1\":\"5.0\",\"m2\":\"5.0\",\"dim1\":\"def\",\"dim2\":[\"abc\"],\"dim3\":[\"m\",\"n\"]}"</span><br /> },<br /> <span class="hljs-attr">"inputFormat"</span>: {<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"json"</span><br /> }<br /> },<br /> <span class="hljs-attr">"tuningConfig"</span>: {<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"index_parallel"</span>,<br /> <span class="hljs-attr">"partitionsSpec"</span>: {<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"dynamic"</span><br /> }<br /> },<br /> <span class="hljs-attr">"dataSchema"</span>: {<br /> <span class="hljs-attr">"dataSource"</span>: <span class="hljs-string">"nested_data"</span>,<br /> <span class="hljs-attr">"granularitySpec"</span>: {<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"uniform"</span>,<br /> <span class="hljs-attr">"queryGranularity"</span>: <span class="hljs-string">"NONE"</span>,<br /> <span class="hljs-attr">"rollup"</span>: <span class="hljs-literal">false</span>,<br /> <span class="hljs-attr">"segmentGranularity"</span>: <span class="hljs-string">"YEAR"</span><br /> },<br /> <span class="hljs-attr">"timestampSpec"</span>: {<br /> <span class="hljs-attr">"column"</span>: <span class="hljs-string">"t"</span>,<br /> <span class="hljs-attr">"format"</span>: <span class="hljs-string">"auto"</span><br /> },<br /> <span class="hljs-attr">"dimensionsSpec"</span>: {<br /> <span class="hljs-attr">"dimensions"</span>: [<br /> <span class="hljs-string">"dim1"</span>,<br /> <span class="hljs-string">"dim2"</span>,<br /> <span class="hljs-string">"dim3"</span>,<br /> <span class="hljs-string">"dim4"</span>,<br /> <span class="hljs-string">"dim5"</span><br /> ]<br /> },<br /> <span class="hljs-attr">"metricsSpec"</span>: [<br /> {<br /> <span class="hljs-attr">"name"</span>: <span class="hljs-string">"m1"</span>,<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"floatSum"</span>,<br /> <span class="hljs-attr">"fieldName"</span>: <span class="hljs-string">"m1"</span><br /> },<br /> {<br /> <span class="hljs-attr">"name"</span>: <span class="hljs-string">"m2"</span>,<br /> <span class="hljs-attr">"type"</span>: <span class="hljs-string">"doubleSum"</span>,<br /> <span class="hljs-attr">"fieldName"</span>: <span class="hljs-string">"m2"</span><br /> }<br /> ]<br /> }<br /> }<br />}<br /></code></pre> |
| </span></div></div></div></div> |
| <h2><a class="anchor" aria-hidden="true" id="view-the-data"></a><a href="#view-the-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>View the data</h2> |
| <p>Now that the data is loaded, run the following query:</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> nested_data |
| </code></pre> |
| <p>In the results, notice that the column named <code>dim3</code> has nested values like <code>["a","b"]</code>. The example queries that follow unnest <code>dim3</code> and run queries against the unnested records. Depending on the type of queries you write, see either <a href="#unnest-using-sql-queries">Unnest using SQL queries</a> or <a href="#unnest-using-native-queries">Unnest using native queries</a>.</p> |
| <h2><a class="anchor" aria-hidden="true" id="unnest-using-sql-queries"></a><a href="#unnest-using-sql-queries" 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>Unnest using SQL queries</h2> |
| <p>The following is the general syntax for UNNEST:</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> column_alias_name <span class="hljs-keyword">FROM</span> datasource, <span class="hljs-keyword">UNNEST</span>(source_expression) <span class="hljs-keyword">AS</span> table_alias_name(column_alias_name) |
| </code></pre> |
| <p>In addition, you must supply the following context parameter:</p> |
| <pre><code class="hljs css language-json">"enableUnnest": "true" |
| </code></pre> |
| <p>For more information about the syntax, see <a href="/docs/latest/querying/sql.html#unnest">UNNEST</a>.</p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-a-single-source-expression-in-a-datasource"></a><a href="#unnest-a-single-source-expression-in-a-datasource" 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>Unnest a single source expression in a datasource</h3> |
| <p>The following query returns a column called <code>d3</code> from the table <code>nested_data</code>. <code>d3</code> contains the unnested values from the source column <code>dim3</code>:</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> d3 <span class="hljs-keyword">FROM</span> <span class="hljs-string">"nested_data"</span>, <span class="hljs-keyword">UNNEST</span>(MV_TO_ARRAY(dim3)) <span class="hljs-keyword">AS</span> example_table(d3) |
| </code></pre> |
| <p>Notice the MV_TO_ARRAY helper function, which converts the multi-value records in <code>dim3</code> to arrays. It is required since <code>dim3</code> is a multi-value string dimension.</p> |
| <p>If the column you are unnesting is not a string dimension, then you do not need to use the MV_TO_ARRAY helper function.</p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-a-virtual-column"></a><a href="#unnest-a-virtual-column" 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>Unnest a virtual column</h3> |
| <p>You can unnest into a virtual column (multiple columns treated as one). The following query returns the two source columns and a third virtual column containing the unnested data:</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> dim4,dim5,d45 <span class="hljs-keyword">FROM</span> nested_data, <span class="hljs-keyword">UNNEST</span>(<span class="hljs-built_in">ARRAY</span>[dim4,dim5]) <span class="hljs-keyword">AS</span> example_table(d45) |
| </code></pre> |
| <p>The virtual column <code>d45</code> is the product of the two source columns. Notice how the total number of rows has grown. The table <code>nested_data</code> had only seven rows originally.</p> |
| <p>Another way to unnest a virtual column is to concatenate them with ARRAY_CONCAT:</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> dim4,dim5,d45 <span class="hljs-keyword">FROM</span> nested_data, <span class="hljs-keyword">UNNEST</span>(ARRAY_CONCAT(dim4,dim5)) <span class="hljs-keyword">AS</span> example_table(d45) |
| </code></pre> |
| <p>Decide which method to use based on what your goals are.</p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-multiple-source-expressions"></a><a href="#unnest-multiple-source-expressions" 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>Unnest multiple source expressions</h3> |
| <p>You can include multiple UNNEST clauses in a single query. Each <code>UNNEST</code> clause needs the following:</p> |
| <pre><code class="hljs css language-sql">UNNEST(source_expression) AS table_alias_name(column_alias_name) |
| </code></pre> |
| <p>The <code>table_alias_name</code> and <code>column_alias_name</code> for each UNNEST clause should be unique.</p> |
| <p>The example query returns the following from the <code>nested_data</code> datasource:</p> |
| <ul> |
| <li>the source columns <code>dim3</code>, <code>dim4</code>, and <code>dim5</code></li> |
| <li>an unnested version of <code>dim3</code> aliased to <code>d3</code></li> |
| <li>an unnested virtual column composed of <code>dim4</code> and <code>dim5</code> aliased to <code>d45</code></li> |
| </ul> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> dim3,dim4,dim5,d3,d45 <span class="hljs-keyword">FROM</span> <span class="hljs-string">"nested_data"</span>, <span class="hljs-keyword">UNNEST</span>(MV_TO_ARRAY(<span class="hljs-string">"dim3"</span>)) <span class="hljs-keyword">AS</span> foo1(d3), <span class="hljs-keyword">UNNEST</span>(<span class="hljs-built_in">ARRAY</span>[dim4,dim5]) <span class="hljs-keyword">AS</span> foo2(d45) |
| </code></pre> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-a-column-from-a-subset-of-a-table"></a><a href="#unnest-a-column-from-a-subset-of-a-table" 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>Unnest a column from a subset of a table</h3> |
| <p>The following query uses only three columns from the <code>nested_data</code> table as the datasource. From that subset, it unnests the column <code>dim3</code> into <code>d3</code> and returns <code>d3</code>.</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> d3 <span class="hljs-keyword">FROM</span> (<span class="hljs-keyword">SELECT</span> dim1, dim2, dim3 <span class="hljs-keyword">FROM</span> <span class="hljs-string">"nested_data"</span>), <span class="hljs-keyword">UNNEST</span>(MV_TO_ARRAY(dim3)) <span class="hljs-keyword">AS</span> example_table(d3) |
| </code></pre> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-with-a-filter"></a><a href="#unnest-with-a-filter" 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>Unnest with a filter</h3> |
| <p>You can specify which rows to unnest by including a filter in your query. The following query:</p> |
| <ul> |
| <li>Filters the source expression based on <code>dim2</code></li> |
| <li>Unnests the records in <code>dim3</code> into <code>d3</code></li> |
| <li>Returns the records for the unnested <code>d3</code> that have a <code>dim2</code> record that matches the filter</li> |
| </ul> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> d3 <span class="hljs-keyword">FROM</span> (<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> nested_data <span class="hljs-keyword">WHERE</span> dim2 <span class="hljs-keyword">IN</span> (<span class="hljs-string">'abc'</span>)), <span class="hljs-keyword">UNNEST</span>(MV_TO_ARRAY(dim3)) <span class="hljs-keyword">AS</span> example_table(d3) |
| </code></pre> |
| <p>You can also filter the results of an UNNEST clause. The following example unnests the inline array <code>[1,2,3]</code> but only returns the rows that match the filter:</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">UNNEST</span>(<span class="hljs-built_in">ARRAY</span>[<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>]) <span class="hljs-keyword">AS</span> example_table(d1) <span class="hljs-keyword">WHERE</span> d1 <span class="hljs-keyword">IN</span> (<span class="hljs-string">'1'</span>,<span class="hljs-string">'2'</span>) |
| </code></pre> |
| <p>This means that you can run a query like the following where Druid only return rows that meet the following conditions:</p> |
| <ul> |
| <li>The unnested values of <code>dim3</code> (aliased to <code>d3</code>) matches <code>IN ('b', 'd')</code></li> |
| <li>The value of <code>m1</code> is less than 2.</li> |
| </ul> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> nested_data, <span class="hljs-keyword">UNNEST</span>(MV_TO_ARRAY(<span class="hljs-string">"dim3"</span>)) <span class="hljs-keyword">AS</span> foo(d3) <span class="hljs-keyword">WHERE</span> d3 <span class="hljs-keyword">IN</span> (<span class="hljs-string">'b'</span>, <span class="hljs-string">'d'</span>) <span class="hljs-keyword">and</span> m1 < <span class="hljs-number">2</span> |
| </code></pre> |
| <p>The query only returns a single row since only one row meets the conditions. You can see the results change if you modify the filter.</p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-and-then-group-by"></a><a href="#unnest-and-then-group-by" 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>Unnest and then GROUP BY</h3> |
| <p>The following query unnests <code>dim3</code> and then performs a GROUP BY on the output <code>d3</code>.</p> |
| <pre><code class="hljs css language-sql"><span class="hljs-keyword">SELECT</span> d3 <span class="hljs-keyword">FROM</span> nested_data, <span class="hljs-keyword">UNNEST</span>(MV_TO_ARRAY(dim3)) <span class="hljs-keyword">AS</span> example_table(d3) <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> d3 |
| </code></pre> |
| <p>You can further transform your results by including clauses like <code>ORDER BY d3 DESC</code> or LIMIT.</p> |
| <h2><a class="anchor" aria-hidden="true" id="unnest-using-native-queries"></a><a href="#unnest-using-native-queries" 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>Unnest using native queries</h2> |
| <p>The following section shows examples of how you can use the unnest datasource in queries. They all use the <code>nested_data</code> table you created earlier in the tutorial.</p> |
| <p>You can use a single unnest datasource to unnest multiple columns. Be careful when doing this though because it can lead to a very large number of new rows.</p> |
| <h3><a class="anchor" aria-hidden="true" id="scan-query"></a><a href="#scan-query" 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>Scan query</h3> |
| <p>The following native Scan query returns the rows of the datasource and unnests the values in the <code>dim3</code> column by using the <code>unnest</code> datasource type:</p> |
| <p><details><summary>Show the query</summary></p> |
| <pre><code class="hljs css language-json">{ |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"scan"</span>, |
| <span class="hljs-attr">"dataSource"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"table"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"nested_data"</span> |
| }, |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"unnest-dim3"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"\"dim3\""</span> |
| } |
| }, |
| <span class="hljs-attr">"intervals"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"intervals"</span>, |
| <span class="hljs-attr">"intervals"</span>: [ |
| <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span> |
| ] |
| }, |
| <span class="hljs-attr">"limit"</span>: <span class="hljs-number">100</span>, |
| <span class="hljs-attr">"columns"</span>: [ |
| <span class="hljs-string">"__time"</span>, |
| <span class="hljs-string">"dim1"</span>, |
| <span class="hljs-string">"dim2"</span>, |
| <span class="hljs-string">"dim3"</span>, |
| <span class="hljs-string">"m1"</span>, |
| <span class="hljs-string">"m2"</span>, |
| <span class="hljs-string">"unnest-dim3"</span> |
| ], |
| <span class="hljs-attr">"legacy"</span>: <span class="hljs-literal">false</span>, |
| <span class="hljs-attr">"granularity"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"all"</span> |
| }, |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"debug"</span>: <span class="hljs-literal">true</span>, |
| <span class="hljs-attr">"useCache"</span>: <span class="hljs-literal">false</span> |
| } |
| } |
| </code></pre> |
| <p></details></p> |
| <p>In the results, notice that there are more rows than before and an additional column named <code>unnest-dim3</code>. The values of <code>unnest-dim3</code> are the same as the <code>dim3</code> column except the nested values are no longer nested and are each a separate record.</p> |
| <p>You can implement filters. For example, you can add the following to the Scan query to filter results to only rows that have the values <code>"a"</code> or <code>"abc"</code> in <code>"dim2"</code>:</p> |
| <pre><code class="hljs css language-json"> "filter": { |
| "type": "in", |
| "dimension": "dim2", |
| "values": [ |
| "a", |
| "abc", |
| ] |
| }, |
| </code></pre> |
| <h3><a class="anchor" aria-hidden="true" id="groupby-query"></a><a href="#groupby-query" 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>groupBy query</h3> |
| <p>The following query returns an unnested version of the column <code>dim3</code> as the column <code>unnest-dim3</code> sorted in descending order.</p> |
| <p><details><summary>Show the query</summary></p> |
| <pre><code class="hljs css language-json">{ |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"groupBy"</span>, |
| <span class="hljs-attr">"dataSource"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: <span class="hljs-string">"nested_data"</span>, |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"unnest-dim3"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"\"dim3\""</span> |
| } |
| }, |
| <span class="hljs-attr">"intervals"</span>: [<span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span>], |
| <span class="hljs-attr">"granularity"</span>: <span class="hljs-string">"all"</span>, |
| <span class="hljs-attr">"dimensions"</span>: [ |
| <span class="hljs-string">"unnest-dim3"</span> |
| ], |
| <span class="hljs-attr">"limitSpec"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"default"</span>, |
| <span class="hljs-attr">"columns"</span>: [ |
| { |
| <span class="hljs-attr">"dimension"</span>: <span class="hljs-string">"unnest-dim3"</span>, |
| <span class="hljs-attr">"direction"</span>: <span class="hljs-string">"descending"</span> |
| } |
| ], |
| <span class="hljs-attr">"limit"</span>: <span class="hljs-number">1001</span> |
| }, |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"debug"</span>: <span class="hljs-literal">true</span> |
| } |
| } |
| </code></pre> |
| <p></details></p> |
| <h3><a class="anchor" aria-hidden="true" id="topn-query"></a><a href="#topn-query" 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>topN query</h3> |
| <p>The example topN query unnests <code>dim3</code> into the column <code>unnest-dim3</code>. The query uses the unnested column as the dimension for the topN query. The results are outputted to a column named <code>topN-unnest-d3</code> and are sorted numerically in ascending order based on the column <code>a0</code>, an aggregate value representing the minimum of <code>m1</code>.</p> |
| <p><details><summary>Show the query</summary></p> |
| <pre><code class="hljs css language-json">{ |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"topN"</span>, |
| <span class="hljs-attr">"dataSource"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"table"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"nested_data"</span> |
| }, |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"unnest-dim3"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"\"dim3\""</span> |
| }, |
| }, |
| <span class="hljs-attr">"dimension"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"default"</span>, |
| <span class="hljs-attr">"dimension"</span>: <span class="hljs-string">"unnest-dim3"</span>, |
| <span class="hljs-attr">"outputName"</span>: <span class="hljs-string">"topN-unnest-d3"</span>, |
| <span class="hljs-attr">"outputType"</span>: <span class="hljs-string">"STRING"</span> |
| }, |
| <span class="hljs-attr">"metric"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"inverted"</span>, |
| <span class="hljs-attr">"metric"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"numeric"</span>, |
| <span class="hljs-attr">"metric"</span>: <span class="hljs-string">"a0"</span> |
| } |
| }, |
| <span class="hljs-attr">"threshold"</span>: <span class="hljs-number">3</span>, |
| <span class="hljs-attr">"intervals"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"intervals"</span>, |
| <span class="hljs-attr">"intervals"</span>: [ |
| <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span> |
| ] |
| }, |
| <span class="hljs-attr">"granularity"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"all"</span> |
| }, |
| <span class="hljs-attr">"aggregations"</span>: [ |
| { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"floatMin"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"a0"</span>, |
| <span class="hljs-attr">"fieldName"</span>: <span class="hljs-string">"m1"</span> |
| } |
| ], |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"debug"</span>: <span class="hljs-literal">true</span> |
| } |
| } |
| </code></pre> |
| <p></details></p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-with-a-join-query"></a><a href="#unnest-with-a-join-query" 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>Unnest with a JOIN query</h3> |
| <p>This query joins the <code>nested_data</code> table with itself and outputs the unnested data into a new column called <code>unnest-dim3</code>.</p> |
| <p><details><summary>Show the query</summary></p> |
| <pre><code class="hljs css language-json">{ |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"scan"</span>, |
| <span class="hljs-attr">"dataSource"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"join"</span>, |
| <span class="hljs-attr">"left"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"table"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"nested_data"</span> |
| }, |
| <span class="hljs-attr">"right"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"query"</span>, |
| <span class="hljs-attr">"query"</span>: { |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"scan"</span>, |
| <span class="hljs-attr">"dataSource"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"table"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"nested_data"</span> |
| }, |
| <span class="hljs-attr">"intervals"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"intervals"</span>, |
| <span class="hljs-attr">"intervals"</span>: [ |
| <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span> |
| ] |
| }, |
| <span class="hljs-attr">"virtualColumns"</span>: [ |
| { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"v0"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"\"m2\""</span>, |
| <span class="hljs-attr">"outputType"</span>: <span class="hljs-string">"FLOAT"</span> |
| } |
| ], |
| <span class="hljs-attr">"resultFormat"</span>: <span class="hljs-string">"compactedList"</span>, |
| <span class="hljs-attr">"columns"</span>: [ |
| <span class="hljs-string">"__time"</span>, |
| <span class="hljs-string">"dim1"</span>, |
| <span class="hljs-string">"dim2"</span>, |
| <span class="hljs-string">"dim3"</span>, |
| <span class="hljs-string">"m1"</span>, |
| <span class="hljs-string">"m2"</span>, |
| <span class="hljs-string">"v0"</span> |
| ], |
| <span class="hljs-attr">"legacy"</span>: <span class="hljs-literal">false</span>, |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"sqlOuterLimit"</span>: <span class="hljs-number">1001</span>, |
| <span class="hljs-attr">"useNativeQueryExplain"</span>: <span class="hljs-literal">true</span> |
| }, |
| <span class="hljs-attr">"granularity"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"all"</span> |
| } |
| } |
| }, |
| <span class="hljs-attr">"rightPrefix"</span>: <span class="hljs-string">"j0."</span>, |
| <span class="hljs-attr">"condition"</span>: <span class="hljs-string">"(\"m1\" == \"j0.v0\")"</span>, |
| <span class="hljs-attr">"joinType"</span>: <span class="hljs-string">"INNER"</span> |
| }, |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"unnest-dim3"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"\"dim3\""</span> |
| } |
| }, |
| <span class="hljs-attr">"intervals"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"intervals"</span>, |
| <span class="hljs-attr">"intervals"</span>: [ |
| <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span> |
| ] |
| }, |
| <span class="hljs-attr">"resultFormat"</span>: <span class="hljs-string">"compactedList"</span>, |
| <span class="hljs-attr">"limit"</span>: <span class="hljs-number">1001</span>, |
| <span class="hljs-attr">"columns"</span>: [ |
| <span class="hljs-string">"__time"</span>, |
| <span class="hljs-string">"dim1"</span>, |
| <span class="hljs-string">"dim2"</span>, |
| <span class="hljs-string">"dim3"</span>, |
| <span class="hljs-string">"j0.__time"</span>, |
| <span class="hljs-string">"j0.dim1"</span>, |
| <span class="hljs-string">"j0.dim2"</span>, |
| <span class="hljs-string">"j0.dim3"</span>, |
| <span class="hljs-string">"j0.m1"</span>, |
| <span class="hljs-string">"j0.m2"</span>, |
| <span class="hljs-string">"m1"</span>, |
| <span class="hljs-string">"m2"</span>, |
| <span class="hljs-string">"unnest-dim3"</span> |
| ], |
| <span class="hljs-attr">"legacy"</span>: <span class="hljs-literal">false</span>, |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"sqlOuterLimit"</span>: <span class="hljs-number">1001</span>, |
| <span class="hljs-attr">"useNativeQueryExplain"</span>: <span class="hljs-literal">true</span> |
| }, |
| <span class="hljs-attr">"granularity"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"all"</span> |
| } |
| } |
| </code></pre> |
| <p></details></p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-a-virtual-column-1"></a><a href="#unnest-a-virtual-column-1" 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>Unnest a virtual column</h3> |
| <p>The <code>unnest</code> datasource supports unnesting virtual columns, which is a queryable composite column that can draw data from multiple source columns.</p> |
| <p>The following query returns the columns <code>dim45</code> and <code>m1</code>. The <code>dim45</code> column is the unnested version of a virtual column that contains an array of the <code>dim4</code> and <code>dim5</code> columns.</p> |
| <p><details><summary>Show the query</summary></p> |
| <pre><code class="hljs css language-json">{ |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"scan"</span>, |
| <span class="hljs-attr">"dataSource"</span>:{ |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"table"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"nested_data"</span> |
| }, |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"dim45"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"array_concat(\"dim4\",\"dim5\")"</span>, |
| <span class="hljs-attr">"outputType"</span>: <span class="hljs-string">"ARRAY<STRING>"</span> |
| }, |
| } |
| <span class="hljs-string">"intervals"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"intervals"</span>, |
| <span class="hljs-attr">"intervals"</span>: [ |
| <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span> |
| ] |
| }, |
| <span class="hljs-attr">"resultFormat"</span>: <span class="hljs-string">"compactedList"</span>, |
| <span class="hljs-attr">"limit"</span>: <span class="hljs-number">1001</span>, |
| <span class="hljs-attr">"columns"</span>: [ |
| <span class="hljs-string">"dim45"</span>, |
| <span class="hljs-string">"m1"</span> |
| ], |
| <span class="hljs-attr">"legacy"</span>: <span class="hljs-literal">false</span>, |
| <span class="hljs-attr">"granularity"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"all"</span> |
| }, |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"debug"</span>: <span class="hljs-literal">true</span>, |
| <span class="hljs-attr">"useCache"</span>: <span class="hljs-literal">false</span> |
| } |
| } |
| </code></pre> |
| <p></details></p> |
| <h3><a class="anchor" aria-hidden="true" id="unnest-a-column-and-a-virtual-column"></a><a href="#unnest-a-column-and-a-virtual-column" 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>Unnest a column and a virtual column</h3> |
| <p>The following Scan query unnests the column <code>dim3</code> into <code>d3</code> and a virtual column composed of <code>dim4</code> and <code>dim5</code> into the column <code>d45</code>. It then returns those source columns and their unnested variants.</p> |
| <p><details><summary>Show the query</summary></p> |
| <pre><code class="hljs css language-json">{ |
| <span class="hljs-attr">"queryType"</span>: <span class="hljs-string">"scan"</span>, |
| <span class="hljs-attr">"dataSource"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"unnest"</span>, |
| <span class="hljs-attr">"base"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"table"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"nested_data"</span> |
| }, |
| |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"d3"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"\"dim3\""</span>, |
| <span class="hljs-attr">"outputType"</span>: <span class="hljs-string">"STRING"</span> |
| }, |
| }, |
| <span class="hljs-attr">"virtualColumn"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"expression"</span>, |
| <span class="hljs-attr">"name"</span>: <span class="hljs-string">"d45"</span>, |
| <span class="hljs-attr">"expression"</span>: <span class="hljs-string">"array(\"dim4\",\"dim5\")"</span>, |
| <span class="hljs-attr">"outputType"</span>: <span class="hljs-string">"ARRAY<STRING>"</span> |
| }, |
| }, |
| <span class="hljs-attr">"intervals"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"intervals"</span>, |
| <span class="hljs-attr">"intervals"</span>: [ |
| <span class="hljs-string">"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"</span> |
| ] |
| }, |
| <span class="hljs-attr">"resultFormat"</span>: <span class="hljs-string">"compactedList"</span>, |
| <span class="hljs-attr">"limit"</span>: <span class="hljs-number">1001</span>, |
| <span class="hljs-attr">"columns"</span>: [ |
| <span class="hljs-string">"dim3"</span>, |
| <span class="hljs-string">"d3"</span>, |
| <span class="hljs-string">"dim4"</span>, |
| <span class="hljs-string">"dim5"</span>, |
| <span class="hljs-string">"d45"</span> |
| ], |
| <span class="hljs-attr">"legacy"</span>: <span class="hljs-literal">false</span>, |
| <span class="hljs-attr">"context"</span>: { |
| <span class="hljs-attr">"enableUnnest"</span>: <span class="hljs-string">"true"</span>, |
| <span class="hljs-attr">"queryId"</span>: <span class="hljs-string">"2618b9ce-6c0d-414e-b88d-16fb59b9c481"</span>, |
| <span class="hljs-attr">"sqlOuterLimit"</span>: <span class="hljs-number">1001</span>, |
| <span class="hljs-attr">"sqlQueryId"</span>: <span class="hljs-string">"2618b9ce-6c0d-414e-b88d-16fb59b9c481"</span>, |
| <span class="hljs-attr">"useNativeQueryExplain"</span>: <span class="hljs-literal">true</span> |
| }, |
| <span class="hljs-attr">"granularity"</span>: { |
| <span class="hljs-attr">"type"</span>: <span class="hljs-string">"all"</span> |
| } |
| } |
| </code></pre> |
| <p></details></p> |
| <h2><a class="anchor" aria-hidden="true" id="learn-more"></a><a href="#learn-more" 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>Learn more</h2> |
| <p>For more information, see the following:</p> |
| <ul> |
| <li><a href="/docs/latest/querying/sql.html#unnest">UNNEST SQL function</a></li> |
| <li><a href="/docs/latest/querying/datasource.html#unnest"><code>unnest</code> in Datasources</a></li> |
| </ul> |
| </span></div></article></div><div class="docs-prevnext"><a class="docs-prev button" href="/docs/latest/tutorials/tutorial-sql-query-view.html"><span class="arrow-prev">← </span><span>Get to know Query view</span></a><a class="docs-next button" href="/docs/latest/tutorials/tutorial-jupyter-index.html"><span>Jupyter Notebook tutorials</span><span class="arrow-next"> →</span></a></div></div></div><nav class="onPageNav"><ul class="toc-headings"><li><a href="#prerequisites">Prerequisites</a></li><li><a href="#load-data-with-nested-values">Load data with nested values</a></li><li><a href="#view-the-data">View the data</a></li><li><a href="#unnest-using-sql-queries">Unnest using SQL queries</a><ul class="toc-headings"><li><a href="#unnest-a-single-source-expression-in-a-datasource">Unnest a single source expression in a datasource</a></li><li><a href="#unnest-a-virtual-column">Unnest a virtual column</a></li><li><a href="#unnest-multiple-source-expressions">Unnest multiple source expressions</a></li><li><a href="#unnest-a-column-from-a-subset-of-a-table">Unnest a column from a subset of a table</a></li><li><a href="#unnest-with-a-filter">Unnest with a filter</a></li><li><a href="#unnest-and-then-group-by">Unnest and then GROUP BY</a></li></ul></li><li><a href="#unnest-using-native-queries">Unnest using native queries</a><ul class="toc-headings"><li><a href="#scan-query">Scan query</a></li><li><a href="#groupby-query">groupBy query</a></li><li><a href="#topn-query">topN query</a></li><li><a href="#unnest-with-a-join-query">Unnest with a JOIN query</a></li><li><a href="#unnest-a-virtual-column-1">Unnest a virtual column</a></li><li><a href="#unnest-a-column-and-a-virtual-column">Unnest a column and a virtual column</a></li></ul></li><li><a href="#learn-more">Learn more</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/latest/">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> |
