1import{s as Mt,p as b,a as r,f as l,c as a,g as u,u as c,j as de,i,W as m,d as o}from"./scheduler.0e87fd8e.js";import{S as Ct,i as At,k as h,b as G,d as X,m as Z,a as x,t as ee,e as te}from"./index.f7b2440f.js";import{D as $t,a as re}from"./DuckDBEditor.8688c02e.js";function Dt(n){let L,s,f,p,v,j="As data enthusiasts working with DuckDB, you might have encountered a common challenge when dealing with arraysâHow to preserve rows with <code>NULL</code> and empty array values during <code>UNNEST</code> operations.",S,w,V="In this guide, weâll explore this issue and provide you with multiple solutions to ensure you donât lose valuable data.",z,I,ae='<a aria-hidden="true" tabindex="-1" href="#the-scenario"><span class="icon icon-link"></span></a>The Scenario',K,y,se="Letâs consider a scenario where you have a table named <code>base</code> with two columns: <code>id</code> and <code>arr</code>. This table contains various rows, some with <code>NULL</code> values in the <code>arr</code> column and others with empty array <code>[]</code>. Your goal is to perform an <code>UNNEST</code> operation on this table, while keeping the <code>NULL</code> and empty array rows intact.",J,_,ne,ie,oe,Q,E,le="In the above query, we want to ensure that rows with <code>NULL</code> and <code>[]</code> are not omitted from the result set. Currently, they are missing from the output. We will fix this.",Y,U,ue='<a aria-hidden="true" tabindex="-1" href="#solution-i-using-union-all"><span class="icon icon-link"></span></a>Solution I: Using UNION ALL',d,C,nt="One way is to use <code>UNION ALL</code> to combine the results of two separate queries. This approach allows you to preserve all rows while <code>UNNEST</code>-ing the non-empty arrays.",ce,N,Be,je,Ve,pe,H,it='<a aria-hidden="true" tabindex="-1" href="#solution-ii-left-join-lateral-with-unnest"><span class="icon icon-link"></span></a>Solution II: LEFT JOIN LATERAL with UNNEST',fe,A,ot="Another way to preserve the rows with <code>NULL</code> and empty array values is by using a <code>LEFT JOIN LATERAL</code> with <code>UNNEST</code>. This approach ensures that all rows from the base table are retained, and <code>UNNEST</code>-ed values are appropriately associated with their corresponding rows.",be,g,ze,Ke,Je,me,P,dt='<a aria-hidden="true" tabindex="-1" href="#solution-iii-using-a-case-statement-with-unnest"><span class="icon icon-link"></span></a>Solution III: Using a CASE Statement with UNNEST',he,$,rt="In this approach, we utilize a <code>CASE</code> statement within the <code>UNNEST</code> operation to handle both <code>NULL</code> and empty array values. If the <code>arr</code> column contains an empty array or is <code>NULL</code>, we replace it with an array containing a single <code>NULL</code> element. This ensures that all rows are preserved during the <code>UNNEST</code> operation, regardless of their original value in the <code>arr</code> column.",Le,k,Qe,Ye,Ge,_e,M,at='<a aria-hidden="true" tabindex="-1" href="#solution-iv-leveraging-if-and-ifnull-for-unnest"><span class="icon icon-link"></span></a>Solution IV: Leveraging IF and IFNULL for UNNEST',ve,T,Xe,Ze,xe,we,D,st="In this solution, we use the <code>IF</code> and <code>IFNULL</code> functions to conditionally determine whether to <code>UNNEST</code> the original <code>arr</code> value or replace it with an array containing a single <code>NULL</code> element.",Ne,F,lt="Hereâs a breakdown of the logic:",ge,R,ut="<li><p><code>IFNULL(arr, [])</code>: This part checks if the <code>arr</code> column is <code>NULL</code>. If itâs <code>NULL</code>, it returns an empty array <code>[]</code>.</p></li> <li><p><code>IF(IFNULL(arr, []) != [], arr, [NULL])</code>: This part checks whether the array is empty or not. If itâs not empty, it <code>UNNEST</code>s the original array <code>arr</code>, preserving its elements. If itâs empty, it replaces it with an array containing a single <code>NULL</code> element.</p></li>",ke,O,ct="By employing these approaches, you can maintain data integrity by preserving rows with <code>NULL</code> and empty arrays during <code>UNNEST</code> operations, ensuring that no valuable information is lost.",Te,Ie,Ue,W,pt="An empty array or an empty list <code>[]</code> and an array or list containing a single <code>NULL</code> element <code>[NULL]</code>
1 are different concepts and have distinct meanings:",ye,q,ft="<li><p>Empty Array/List <code>[]</code>: An empty array or list represents a collection with no elements. It is essentially a container with zero items. In SQL, an empty array signifies that there are no valid elements in the array.</p></li> <li><p>Array/List with NULL Element <code>[NULL]</code>: Represents an array or list that contains one element, and that element is <code>NULL</code>. In SQL, this indicates the presence of a single element, which is explicitly set to a <code>NULL</code> value.</p></li>",Ee,Se,He,B,bt="Try out those approaches in your DuckDB projects and stay tuned for more informative recipes in the Ducklicious DuckDB Cookbook.",Pe;function mt(e){n[7](e)}function ht(e){n[8](e)}let et={};n[0]!==void 0&&(et.connProm=n[0]),n[1]!==void 0&&(et.dbInit=n[1]),L=new $t({props:et}),b.push(()=>h(L,"connProm",mt)),b.push(()=>h(L,"dbInit",ht));function Lt(e){n[9](e)}function _t(e){n[10](e)}function vt(e){n[11](e)}let Me={};n[2]!==void 0&&(Me.value=n[2]),n[0]!==void 0&&(Me.connProm=n[0]),n[1]!==void 0&&(Me.dbInit=n[1]),_=new re({props:Me}),b.push(()=>h(_,"value",Lt)),b.push(()=>h(_,"connProm",_t)),b.push(()=>h(_,"dbInit",vt));function wt(e){n[12](e)}function Nt(e){n[13](e)}function gt(e){n[14](e)}let Ce={};n[3]!==void 0&&(Ce.value=n[3]),n[0]!==void 0&&(Ce.connProm=n[0]),n[1]!==void 0&&(Ce.dbInit=n[1]),N=new re({props:Ce}),b.push(()=>h(N,"value",wt)),b.push(()=>h(N,"connProm",Nt)),b.push(()=>h(N,"dbInit",gt));function kt(e){n[15](e)}function Tt(e){n[16](e)}function It(e){n[17](e)}let Ae={};n[4]!==void 0&&(Ae.value=n[4]),n[0]!==void 0&&(Ae.connProm=n[0]),n[1]!==void 0&&(Ae.dbInit=n[1]),g=new re({props:Ae}),b.push(()=>h(g,"value",kt)),b.push(()=>h(g,"connProm",Tt)),b.push(()=>h(g,"dbInit",It));function Ut(e){n[18](e)}function yt(e){n[19](e)}function Et(e){n[20](e)}let $e={};n[5]!==void 0&&($e.value=n[5]),n[0]!==void 0&&($e.connProm=n[0]),n[1]!==void 0&&($e.dbInit=n[1]),k=new re({props:$e}),b.push(()=>h(k,"value",Ut)),b.push(()=>h(k,"connProm",yt)),b.push(()=>h(k,"dbInit",Et));function St(e){n[21](e)}function Ht(e){n[22](e)}function Pt(e){n[23](e)}let De={};return n[6]!==void 0&&(De.value=n[6]),n[0]!==void 0&&(De.connProm=n[0]),n[1]!==void 0&&(De.dbInit=n[1]),T=new re({props:De}),b.push(()=>h(T,"value",St)),b.push(()=>h(T,"connProm",Ht)),b.push(()=>h(T,"dbInit",Pt)),{c(){G(L.$$.fragment),p=r(),v=l("p"),v.innerHTML=j,S=r(),w=l("p"),w.textContent=V,z=r(),I=l("h2"),I.innerHTML=ae,K=r(),y=l("p"),y.innerHTML=se,J=r(),G(_.$$.fragment),Q=r(),E=l("p"),E.innerHTML=le,Y=r(),U=l("h2"),U.innerHTML=ue,d=r(),C=l("p"),C.innerHTML=nt,ce=r(),G(N.$$.fragment),pe=r(),H=l("h2"),H.innerHTML=it,fe=r(),A=l("p"),A.innerHTML=ot,be=r(),G(g.$$.fragment),me=r(),P=l("h2"),P.innerHTML=dt,he=r(),$=l("p"),$.innerHTML=rt,Le=r(),G(k.$$.fragment),_e=r(),M=l("h2"),M.innerHTML=at,ve=r(),G(T.$$.fragment),we=r(),D=l("p"),D.innerHTML=st,Ne=r(),F=l("p"),F.textContent=lt,ge=r(),R=l("ul"),R.innerHTML=ut,ke=r(),O=l("p"),O.innerHTML=ct,Te=r(),Ie=l("hr"),Ue=r(),W=l("p"),W.innerHTML=pt,ye=r(),q=l("ul"),q.innerHTML=ft,Ee=r(),Se=l("hr"),He=r(),B=l("p"),B.textContent=bt,this.h()},l(e){X(L.$$.fragment,e),p=a(e),v=u(e,"P",{"data-svelte-h":!0}),c(v)!=="svelte-1afilpm"&&(v.innerHTML=j),S=a(e),w=u(e,"P",{"data-svelte-h":!0}),c(w)!=="svelte-fy6yfs"&&(w.textContent=V),z=a(e),I=u(e,"H2",{id:!0,"data-svelte-h":!0}),c(I)!=="svelte-h7qq05"&&(I.innerHTML=ae),K=a(e),y=u(e,"P",{"data-svelte-h":!0}),c(y)!=="svelte-bl0jk0"&&(y.innerHTML=se),J=a(e),X(_.$$.fragment,e),Q=a(e),E=u(e,"P",{"data-svelte-h":!0}),c(E)!=="svelte-optnci"&&(E.innerHTML=le),Y=a(e),U=u(e,"H2",{id:!0,"data-svelte-h":!0}),c(U)!=="svelte-1mdb56i"&&(U.innerHTML=ue),d=a(e),C=u(e,"P",{"data-svelte-h":!0}),c(C)!=="svelte-1ecp82n"&&(C.innerHTML=nt),ce=a(e),X(N.$$.fragment,e),pe=a(e),H=u(e,"H2",{id:!0,"data-svelte-h":!0}),c(H)!=="svelte-4iu9z6"&&(H.innerHTML=it),fe=a(e),A=u(e,"P",{"data-svelte-h":!0}),c(A)!=="svelte-kzem2w"&&(A.innerHTML=ot),be=a(e),X(g.$$.fragment,e),me=a(e),P=u(e,"H2",{id:!0,"data-svelte-h":!0}),c(P)!=="svelte-1aqln9t"&&(P.innerHTML=dt),he=a(e),$=u(e,"P",{"data-svelte-h":!0}),c($)!=="svelte-w0rq45"&&($.innerHTML=rt),Le=a(e),X(k.$$.fragment,e),_e=a(e),M=u(e,"H2",{id:!0,"data-svelte-h":!0}),c(M)!=="svelte-1lneozw"&&(M.innerHTML=at),ve=a(e),X(T.$$.fragment,e),we=a(e),D=u(e,"P",{"data-svelte-h":!0}),c(D)!=="svelte-1y94m3m"&&(D.innerHTML=st),Ne=a(e),F=u(e,"P",{"data-svelte-h":!0}),c(F)!=="svelte-18ju230"&&(F.textContent=lt),ge=a(e),R=u(e,"UL",{"data-svelte-h":!0}),c(R)!=="svelte-73n0wa"&&(R.innerHTML=ut),ke=a(e),O=u(e,"P",{"data-svelte-h":!0}),c(O)!=="svelte-1e3uwx0"&&(O.innerHTML=ct),Te=a(e),Ie=u(e,"HR",{}),Ue=a(e),W=u(e,"P",{"data-svelte-h":!0}),c(W)!=="svelte-1n8301x"&&(W.innerHTML=pt),ye=a(e),q=u(e,"UL",{"data-svelte-h":!0}),c(q)!=="svelte-1q4scik"&&(q.innerHTML=ft),Ee=a(e),Se=u(e,"HR",{}),He=a(e),B=u(e,"P",{"data-svelte-h":!0}),c(B)!=="svelte-qaj3h"&&(B.textContent=bt),this.h()},h(){de(I,"id","the-scenario"),de(U,"id","solution-i-using-union-all"),de(H,"id","solution-ii-left-join-lateral-with-unnest"),de(P,"id","solution-iii-using-a-case-statement-with-unnest"),de(M,"id","solution-iv-leveraging-if-and-ifnull-for-unnest")},m(e,t){Z(L,e,t),i(e,p,t),i(e,v,t),i(e,S,t),i(e,w,t),i(e,z,t),i(e,I,t),i(e,K,t),i(e,y,t),i(e,J,t),Z(_,e,t),i(e,Q,t),i(e,E,t),i(e,Y,t),i(e,U,t),i(e,d,t),i(e,C,t),i(e,ce,t),Z(N,e,t),i(e,pe,t),i(e,H,t),i(e,fe,t),i(e,A,t),i(e,be,t),Z(g,e,t),i(e,me,t),i(e,P,t),i(e,he,t),i(e,$,t),i(e,Le,t),Z(k,e,t),i(e,_e,t),i(e,M,t),i(e,ve,t),Z(T,e,t),i(e,we,t),i(e,D,t),i(e,
1Ne,t),i(e,F,t),i(e,ge,t),i(e,R,t),i(e,ke,t),i(e,O,t),i(e,Te,t),i(e,Ie,t),i(e,Ue,t),i(e,W,t),i(e,ye,t),i(e,q,t),i(e,Ee,t),i(e,Se,t),i(e,He,t),i(e,B,t),Pe=!0},p(e,[t]){const tt={};!s&&t&1&&(s=!0,tt.connProm=e[0],m(()=>s=!1)),!f&&t&2&&(f=!0,tt.dbInit=e[1],m(()=>f=!1)),L.$set(tt);const Fe={};!ne&&t&4&&(ne=!0,Fe.value=e[2],m(()=>ne=!1)),!ie&&t&1&&(ie=!0,Fe.connProm=e[0],m(()=>ie=!1)),!oe&&t&2&&(oe=!0,Fe.dbInit=e[1],m(()=>oe=!1)),_.$set(Fe);const Re={};!Be&&t&8&&(Be=!0,Re.value=e[3],m(()=>Be=!1)),!je&&t&1&&(je=!0,Re.connProm=e[0],m(()=>je=!1)),!Ve&&t&2&&(Ve=!0,Re.dbInit=e[1],m(()=>Ve=!1)),N.$set(Re);const Oe={};!ze&&t&16&&(ze=!0,Oe.value=e[4],m(()=>ze=!1)),!Ke&&t&1&&(Ke=!0,Oe.connProm=e[0],m(()=>Ke=!1)),!Je&&t&2&&(Je=!0,Oe.dbInit=e[1],m(()=>Je=!1)),g.$set(Oe);const We={};!Qe&&t&32&&(Qe=!0,We.value=e[5],m(()=>Qe=!1)),!Ye&&t&1&&(Ye=!0,We.connProm=e[0],m(()=>Ye=!1)),!Ge&&t&2&&(Ge=!0,We.dbInit=e[1],m(()=>Ge=!1)),k.$set(We);const qe={};!Xe&&t&64&&(Xe=!0,qe.value=e[6],m(()=>Xe=!1)),!Ze&&t&1&&(Ze=!0,qe.connProm=e[0],m(()=>Ze=!1)),!xe&&t&2&&(xe=!0,qe.dbInit=e[1],m(()=>xe=!1)),T.$set(qe)},i(e){Pe||(x(L.$$.fragment,e),x(_.$$.fragment,e),x(N.$$.fragment,e),x(g.$$.fragment,e),x(k.$$.fragment,e),x(T.$$.fragment,e),Pe=!0)},o(e){ee(L.$$.fragment,e),ee(_.$$.fragment,e),ee(N.$$.fragment,e),ee(g.$$.fragment,e),ee(k.$$.fragment,e),ee(T.$$.fragment,e),Pe=!1},d(e){e&&(o(p),o(v),o(S),o(w),o(z),o(I),o(K),o(y),o(J),o(Q),o(E),o(Y),o(U),o(d),o(C),o(ce),o(pe),o(H),o(fe),o(A),o(be),o(me),o(P),o(he),o($),o(Le),o(_e),o(M),o(ve),o(we),o(D),o(Ne),o(F),o(ge),o(R),o(ke),o(O),o(Te),o(Ie),o(Ue),o(W),o(ye),o(q),o(Ee),o(Se),o(He),o(B)),te(L,e),te(_,e),te(N,e),te(g,e),te(k,e),te(T,e)}}}const qt={category:"duckdb-cookbook",order:1,title:"DuckDB UNNEST: Preserving Rows with NULL and Empty Array",date:"2023-10-06",updated:"2023-10-06",isPublished:!0,excerpt:"How to preserve rows with NULL and empty array values during UNNEST operations.",preview:`As data enthusiasts working with DuckDB, you might have encountered a common challenge when dealing with arraysâHow to preserve rows with and empty array values during operations. 2 3In this guide, weâll explore this issue and provide you with...`,previewHtml:`<p>As data enthusiasts working with DuckDB, you might have encountered a common challenge when dealing with arraysâHow to preserve rows with <code>NULL</code> and empty array values during <code>UNNEST</code> operations. </p> 4<p>In this guide, weâll explore this issue and provide you with multiple solutions to ensure you donât lose valuable data.</p> 5<h2>The Scenario</h2> 6<p>Letâs consider a scenario where you have a table named <code>base</code> with two columns: <code>id</code> and <code>arr</code>. This table contains various rows, some with <code>NULL</code> values in the <code>arr</code> column and others with empty array <code>[]</code>. Your goal is to perform an <code>UNNEST</code> operation on this table, while keeping the <code>NULL</code> and empty array rows intact.</p>`};function Ft(n,L,s){let f,p,v=`WITH base(id, arr) AS ( 7 VALUES 8 (1, NULL), 9 (2, [1,2,3]), 10 (3, []) 11) 12-- Missing NULL and [] rows 13SELECT 14 id, 15 UNNEST(arr) AS val 16FROM base`,j=`WITH base(id, arr) AS ( 17 VALUES 18 (1, NULL), 19 (2, [1,2,3]), 20 (3, []) 21) 22-- Keeping NULL and [] rows 23SELECT 24 id, 25 UNNEST(arr) 26FROM base 27WHERE arr IS NOT NULL 28 29UNION ALL 30 31SELECT 32 id, 33 NULL 34FROM base 35WHERE arr IS NULL 36 OR arr = []`,S=`WITH base(id, arr) AS ( 37 VALUES 38 (1, NULL), 39 (2, [1,2,3]), 40 (3, []) 41) 42-- Keeping NULL and [] rows 43SELECT 44 id, 45 arr.val 46FROM base 47LEFT JOIN LATERAL UNNEST(arr) AS arr(val) ON TRUE`,w=`WITH base(id, arr) AS ( 48 VALUES 49 (1, NULL), 50 (2, [1,2,3]), 51 (3, []) 52) 53-- Keeping NULL and [] rows 54SELECT 55 id, 56 UNNEST( 57 CASE WHEN arr = [] THEN [null] 58 WHEN arr IS NULL THEN [null] 59 ELSE arr 60 END 61 ) AS val 62FROM base`,V=`WITH base(id, arr) AS ( 63 VALUES 64 (1, NULL), 65 (2, [1,2,3]), 66 (3, []) 67) 68-- Keeping NULL and [] rows 69SELECT 70 id, 71 UNNEST(IF(IFNULL(arr, []) != [], arr, [null])) AS val 72FROM base`;function z(d){f=d,s(0,f)}function I(d){p=d,s(1,p)}function ae(d){v=d,s(2,v)}function K(d){f=d,s(0,f)}function y(d){p=d,s(1,p)}
72function se(d){j=d,s(3,j)}function J(d){f=d,s(0,f)}function _(d){p=d,s(1,p)}function ne(d){S=d,s(4,S)}function ie(d){f=d,s(0,f)}function oe(d){p=d,s(1,p)}function Q(d){w=d,s(5,w)}function E(d){f=d,s(0,f)}function le(d){p=d,s(1,p)}function Y(d){V=d,s(6,V)}function U(d){f=d,s(0,f)}function ue(d){p=d,s(1,p)}return[f,p,v,j,S,w,V,z,I,ae,K,y,se,J,_,ne,ie,oe,Q,E,le,Y,U,ue]}class Bt extends Ct{constructor(L){super(),At(this,L,Ft,Dt,Mt,{})}}export{Bt as default,qt as metadata};
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.