1"use strict";(globalThis.webpackChunkstackql_io||=[]).push([[5415],{80851(e,n,s){s.r(n),s.d(n,{assets:()=>i,contentTitle:()=>a,default:()=>h,frontMatter:()=>c,metadata:()=>r,toc:()=>l});const r=JSON.parse('{"id":"language-spec/functions/json/json_each","title":"JSON_EACH","description":"Query and Deploy Cloud Infrastructure and Resources using SQL","source":"@site/docs/language-spec/functions/json/json_each.md","sourceDirName":"language-spec/functions/json","slug":"/language-spec/functions/json/json_each","permalink":"/docs/language-spec/functions/json/json_each","draft":false,"unlisted":false,"editUrl":"https://github.com/stackql/stackql.io/edit/main/docs/language-spec/functions/json/json_each.md","tags":[],"version":"current","lastUpdatedAt":1708296978000,"frontMatter":{"title":"JSON_EACH","hide_title":false,"hide_table_of_contents":false,"keywords":["stackql","infrastructure-as-code","configuration-as-data","cloud inventory"],"description":"Query and Deploy Cloud Infrastructure and Resources using SQL","image":"/img/stackql-featured-image.png"},"sidebar":"docsSidebar","previous":{"title":"JSON_ARRAY_LENGTH","permalink":"/docs/language-spec/functions/json/json_array_length"},"next":{"title":"JSON_EQUAL","permalink":"/docs/language-spec/functions/json/json_equal"}}');var o=s(74848),t=s(28453);const c={title:"JSON_EACH",hide_title:!1,hide_table_of_contents:!1,keywords:["stackql","infrastructure-as-code","configuration-as-data","cloud inventory"],description:"Query and Deploy Cloud Infrastructure and Resources using SQL",image:"/img/stackql-featured-image.png"},a=void 0,i={},l=[{value:"Syntax",id:"syntax",level:2},{value:"Arguments",id:"arguments",level:2},{value:"Return Value(s)",id:"return-values",level:2},{value:"Examples",id:"examples",level:2},{value:"Unnest an array of strings into a table joined with associated fields",id:"unnest-an-array-of-strings-into-a-table-joined-with-associated-fields",level:3}];function d(e){const n={a:"a",admonition:"admonition",blockquote:"blockquote",br:"br",code:"code",em:"em",h2:"h2",h3:"h3",hr:"hr",mdxAdmonitionTitle:"mdxAdmonitionTitle",p:"p",pre:"pre",strong:"strong",...(0,t.R)(),...e.components};return(0,o.jsxs)(o.Fragment,{children:[(0,o.jsx)(n.p,{children:"Table-valued function which returns a table consisting of one row for each array element or object member."}),"\n",(0,o.jsxs)(n.p,{children:["See also:",(0,o.jsx)(n.br,{}),"\n",(0,o.jsxs)(n.a,{href:"/docs/language-spec/select",children:["[",(0,o.jsx)(n.code,{children:"SELECT"}),"]"]})," ",(0,o.jsxs)(n.a,{href:"/docs/language-spec/describe",children:["[",(0,o.jsx)(n.code,{children:"DESCRIBE"}),"]"]})," ",(0,o.jsxs)(n.a,{href:"/docs/language-spec/functions/json/json_extract",children:["[",(0,o.jsx)(n.code,{children:"JSON_EXTRACT"}),"]"]})," ",(0,o.jsxs)(n.a,{href:"/docs/language-spec/functions/json/json_array_length",children:["[",(0,o.jsx)(n.code,{children:"JSON_ARRAY_LENGTH"}),"]"]})," ",(0,o.jsx)(n.a,{href:"/docs/language-spec/data-types",children:"[ Data Types ]"})]}),"\n",(0,o.jsx)(n.hr,{}),"\n",(0,o.jsxs)(n.admonition,{type:"tip",children:[(0,o.jsx)(n.mdxAdmonitionTitle,{}),(0,o.jsxs)(n.p,{children:["Use the ",(0,o.jsx)(n.a,{href:"/docs/language-spec/describe",children:(0,o.jsx)(n.strong,{children:"DESCRIBE"})})," function to locate ",(0,o.jsx)(n.code,{children:"array"})," datatypes which can be used with the ",(0,o.jsx)(n.code,{children:"json_each"})," function."]})]}),"\n",(0,o.jsxs)(n.admonition,{type:"note",children:[(0,o.jsx)(n.mdxAdmonitionTitle,{}),(0,o.jsxs)(n.p,{children:["The ",(0,o.jsx)(n.code,{children:"json_each"})," function is implemented as the ",(0,o.jsx)(n.code,{children:"json_array_elements_text"})," function if you are using stackql with a ",(0,o.jsx)(n.code,{children:"postgres"})," sql backend"]}
1)]}),"\n",(0,o.jsx)(n.h2,{id:"syntax",children:"Syntax"}),"\n",(0,o.jsx)(n.pre,{children:(0,o.jsx)(n.code,{className:"language-sql",children:"SELECT <alias>.<projectedField>, ... , json_each.value \r\nFROM <multipartIdentifier> <alias>, JSON_EACH(json_array_or_object, json_path);\n"})}),"\n",(0,o.jsx)(n.h2,{id:"arguments",children:"Arguments"}),"\n",(0,o.jsxs)(n.p,{children:[(0,o.jsx)(n.strong,{children:(0,o.jsx)(n.em,{children:"json_array_or_object"})}),(0,o.jsx)(n.br,{}),"\n","A JSON array or object."]}),"\n",(0,o.jsxs)(n.p,{children:[(0,o.jsx)(n.strong,{children:(0,o.jsx)(n.em,{children:"json_path"})}),(0,o.jsx)(n.br,{}),"\n","The JSON path to an array or object contained within an ",(0,o.jsx)(n.em,{children:"json_array_or_object"}),". See ",(0,o.jsx)(n.a,{href:"https://jsonpath.com/",children:"jsonpath.com"}),"."]}),"\n",(0,o.jsxs)(n.blockquote,{children:["\n",(0,o.jsxs)(n.p,{children:["If ",(0,o.jsx)(n.em,{children:"json_path"})," does not exist in ",(0,o.jsx)(n.em,{children:"json_array_or_object"}),", then the function returns ",(0,o.jsx)(n.code,{children:"NULL"}),"."]}),"\n"]}),"\n",(0,o.jsxs)(n.blockquote,{children:["\n",(0,o.jsxs)(n.p,{children:["The ",(0,o.jsx)(n.code,{children:"json_type()"})," function throws an error if any of its arguments are not well-formed."]}),"\n"]}),"\n",(0,o.jsx)(n.h2,{id:"return-values",children:"Return Value(s)"}),"\n",(0,o.jsx)(n.p,{children:"A table consisting of one row for each array element or object member."}),"\n",(0,o.jsx)(n.hr,{}),"\n",(0,o.jsx)(n.h2,{id:"examples",children:"Examples"}),"\n",(0,o.jsx)(n.h3,{id:"unnest-an-array-of-strings-into-a-table-joined-with-associated-fields",children:"Unnest an array of strings into a table joined with associated fields"}),"\n",(0,o.jsx)(n.p,{children:"for context:"}),"\n",(0,o.jsx)(n.pre,{children:(0,o.jsx)(n.code,{className:"language-sql",children:"describe google.cloudresourcemanager.projects_iam_policies;\n"})}),"\n",(0,o.jsx)(n.p,{children:"returns..."}),"\n",(0,o.jsx)(n.pre,{children:(0,o.jsx)(n.code,{children:"|-----------|--------| \r\n| name | type | \r\n|-----------|--------| \r\n| condition | object | \r\n|-----------|--------| \r\n| members | array | \r\n|-----------|--------| \r\n| role | string | \r\n|-----------|--------| \n"})}),"\n",(0,o.jsxs)(n.p,{children:["The following ",(0,o.jsx)(n.code,{children:"stackql"})," query uses the ",(0,o.jsx)(n.code,{children:"json_each"})," function to unnest the ",(0,o.jsx)(n.code,{children:"members"})," array into a table joined with the ",(0,o.jsx)(n.code,{children:"role"})," and optionally ",(0,o.jsx)(n.code,{children:"condition"})," fields."]}),"\n",(0,o.jsx)(n.pre,{children:(0,o.jsx)(n.code,{className:"language-sql",children:"select \r\niam.role, \r\nSPLIT_PART(json_each.value, ':', 2) as member \r\nfrom google.cloudresourcemanager.projects_iam_policies iam, json_each(members) \r\nwhere projectsId = 'stackql';\n"})}),"\n",(0,o.jsx)(n.p,{children:"would return..."}),"\n",(0,o.jsx)(n.pre,{children:(0,o.jsx)(n.code,{children:"|--------------------------------------|-------------------------------------------------------| \r\n| role | member | \r\n|--------------------------------------|-------------------------------------------------------| \r\n| roles/cloudbuild.builds.builder | [email protected] | \r\n|--------------------------------------|---------------------------------------------- --------| \r\n| roles/editor | [email protected] | \r\n|--------------------------------------|-------------------------------------------------------| \r\n| roles/editor | [email protected] | \r\n|--------------------------------------|-------------------------------------------------------| \r\n| roles/editor | [email protected] | \r\n|--------------------------------------|-------------------------------------------------------| \r\n| ... | ... | \r\n|--------------------------------------|-------------------------------------------------------| \n"})}),"\n",(0,o.jsxs)(n.p,{children:["For more information, see ",(0,o.jsx)(n.a,{href:"https://www.sqlite.org/json1.html#jeach",children:"https://www.sqlite.org/json1.html#jeach"})]})]})}function h(e={}){const{wrapper:n}={...(0,t.R)(),...e.components};return n?(0,o.jsx)(n,{...e,children:(0,o.jsx)(d,{...e})}):d(e)}},28453(e,n,s){s.d(n,{R:()=>c,x:()=>a});var r=s(96540);const o={},t=r.createContext(o);function c(e){const n=r.useContext(t);return r.useMemo(function(){return"function"==typeof e?e(n):{...n,...e}},[n,e])}function a(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(o):e.components||o:c(e.components),r.createElement(t.Provider,{value:n},e.children)}}}]);
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.