| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[6589],{15680:(e,t,n)=>{n.d(t,{xA:()=>d,yg:()=>m});var a=n(96540);function r(e,t,n){return t in e?Object.defineProperty(e,t,{value:n,enumerable:!0,configurable:!0,writable:!0}):e[t]=n,e}function i(e,t){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var a=Object.getOwnPropertySymbols(e);t&&(a=a.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),n.push.apply(n,a)}return n}function l(e){for(var t=1;t<arguments.length;t++){var n=null!=arguments[t]?arguments[t]:{};t%2?i(Object(n),!0).forEach((function(t){r(e,t,n[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(n)):i(Object(n)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(n,t))}))}return e}function o(e,t){if(null==e)return{};var n,a,r=function(e,t){if(null==e)return{};var n,a,r={},i=Object.keys(e);for(a=0;a<i.length;a++)n=i[a],t.indexOf(n)>=0||(r[n]=e[n]);return r}(e,t);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);for(a=0;a<i.length;a++)n=i[a],t.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(e,n)&&(r[n]=e[n])}return r}var p=a.createContext({}),s=function(e){var t=a.useContext(p),n=t;return e&&(n="function"==typeof e?e(t):l(l({},t),e)),n},d=function(e){var t=s(e.components);return a.createElement(p.Provider,{value:t},e.children)},y="mdxType",g={inlineCode:"code",wrapper:function(e){var t=e.children;return a.createElement(a.Fragment,{},t)}},u=a.forwardRef((function(e,t){var n=e.components,r=e.mdxType,i=e.originalType,p=e.parentName,d=o(e,["components","mdxType","originalType","parentName"]),y=s(n),u=r,m=y["".concat(p,".").concat(u)]||y[u]||g[u]||i;return n?a.createElement(m,l(l({ref:t},d),{},{components:n})):a.createElement(m,l({ref:t},d))}));function m(e,t){var n=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var i=n.length,l=new Array(i);l[0]=u;var o={};for(var p in t)hasOwnProperty.call(t,p)&&(o[p]=t[p]);o.originalType=e,o[y]="string"==typeof e?e:r,l[1]=o;for(var s=2;s<i;s++)l[s]=n[s];return a.createElement.apply(null,l)}return a.createElement.apply(null,n)}u.displayName="MDXCreateElement"},68666:(e,t,n)=>{n.r(t),n.d(t,{assets:()=>d,contentTitle:()=>p,default:()=>m,frontMatter:()=>o,metadata:()=>s,toc:()=>y});var a=n(58168),r=n(98587),i=(n(96540),n(15680)),l=["components"],o={id:"sql-json-functions",title:"SQL JSON functions",sidebar_label:"JSON functions"},p=void 0,s={unversionedId:"querying/sql-json-functions",id:"querying/sql-json-functions",title:"SQL JSON functions",description:"\x3c!--",source:"@site/docs/29.0.0/querying/sql-json-functions.md",sourceDirName:"querying",slug:"/querying/sql-json-functions",permalink:"/docs/29.0.0/querying/sql-json-functions",draft:!1,tags:[],version:"current",frontMatter:{id:"sql-json-functions",title:"SQL JSON functions",sidebar_label:"JSON functions"},sidebar:"docs",previous:{title:"Multi-value string functions",permalink:"/docs/29.0.0/querying/sql-multivalue-string-functions"},next:{title:"All functions",permalink:"/docs/29.0.0/querying/sql-functions"}},d={},y=[{value:"JSONPath syntax",id:"jsonpath-syntax",level:3}],g={toc:y},u="wrapper";function m(e){var t=e.components,n=(0,r.A)(e,l);return(0,i.yg)(u,(0,a.A)({},g,n,{components:t,mdxType:"MDXLayout"}),(0,i.yg)("p",null,"Druid supports nested columns, which provide optimized storage and indexes for nested data structures. See ",(0,i.yg)("a",{parentName:"p",href:"/docs/29.0.0/querying/nested-columns"},"Nested columns")," for more information."),(0,i.yg)("p",null,"You can use the following JSON functions to extract, transform, and create ",(0,i.yg)("inlineCode",{parentName:"p"},"COMPLEX<json>")," values."),(0,i.yg)("table",null,(0,i.yg)("thead",{parentName:"table"},(0,i.yg)("tr",{parentName:"thead"},(0,i.yg)("th",{parentName:"tr",align:null},"Function"),(0,i.yg)("th",{parentName:"tr",align:null},"Notes"))),(0,i.yg)("tbody",{parentName:"table"},(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"JSON_KEYS(expr, path)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns an array of field names from ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," at the specified ",(0,i.yg)("inlineCode",{parentName:"td"},"path"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])")),(0,i.yg)("td",{parentName:"tr",align:null},"Constructs a new ",(0,i.yg)("inlineCode",{parentName:"td"},"COMPLEX<json>")," object. The ",(0,i.yg)("inlineCode",{parentName:"td"},"KEY")," expressions must evaluate to string types. The ",(0,i.yg)("inlineCode",{parentName:"td"},"VALUE")," expressions can be composed of any input type, including other ",(0,i.yg)("inlineCode",{parentName:"td"},"COMPLEX<json>")," values. ",(0,i.yg)("inlineCode",{parentName:"td"},"JSON_OBJECT")," can accept colon-separated key-value pairs. The following syntax is equivalent: ",(0,i.yg)("inlineCode",{parentName:"td"},"JSON_OBJECT(expr1:expr2[, expr3:expr4, ...])"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"JSON_PATHS(expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Returns an array of all paths which refer to literal values in ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," in JSONPath format.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"JSON_QUERY(expr, path)")),(0,i.yg)("td",{parentName:"tr",align:null},"Extracts a ",(0,i.yg)("inlineCode",{parentName:"td"},"COMPLEX<json>")," value from ",(0,i.yg)("inlineCode",{parentName:"td"},"expr"),", at the specified ",(0,i.yg)("inlineCode",{parentName:"td"},"path"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"JSON_QUERY_ARRAY(expr, path)")),(0,i.yg)("td",{parentName:"tr",align:null},"Extracts an ",(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY<COMPLEX<json>>")," value from ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," at the specified ",(0,i.yg)("inlineCode",{parentName:"td"},"path"),". If value is not an ",(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY"),", it gets translated into a single element ",(0,i.yg)("inlineCode",{parentName:"td"},"ARRAY")," containing the value at ",(0,i.yg)("inlineCode",{parentName:"td"},"path"),". The primary use of this function is to extract arrays of objects to use as inputs to other ",(0,i.yg)("a",{parentName:"td",href:"/docs/29.0.0/querying/sql-array-functions"},"array functions"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"JSON_VALUE(expr, path [RETURNING sqlType])")),(0,i.yg)("td",{parentName:"tr",align:null},"Extracts a literal value from ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," at the specified ",(0,i.yg)("inlineCode",{parentName:"td"},"path"),". If you specify ",(0,i.yg)("inlineCode",{parentName:"td"},"RETURNING")," and an SQL type name (such as ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR"),", ",(0,i.yg)("inlineCode",{parentName:"td"},"BIGINT"),", ",(0,i.yg)("inlineCode",{parentName:"td"},"DOUBLE"),", etc) the function plans the query using the suggested type. Otherwise, it attempts to infer the type based on the context. If it can't infer the type, it defaults to ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR"),".")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"PARSE_JSON(expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Parses ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," into a ",(0,i.yg)("inlineCode",{parentName:"td"},"COMPLEX<json>")," object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR")," or it is invalid JSON, this function will result in an error.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"TRY_PARSE_JSON(expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Parses ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," into a ",(0,i.yg)("inlineCode",{parentName:"td"},"COMPLEX<json>")," object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a ",(0,i.yg)("inlineCode",{parentName:"td"},"VARCHAR")," or it is invalid JSON, this function will result in a ",(0,i.yg)("inlineCode",{parentName:"td"},"NULL")," value.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"TO_JSON_STRING(expr)")),(0,i.yg)("td",{parentName:"tr",align:null},"Serializes ",(0,i.yg)("inlineCode",{parentName:"td"},"expr")," into a JSON string.")))),(0,i.yg)("h3",{id:"jsonpath-syntax"},"JSONPath syntax"),(0,i.yg)("p",null,"Druid supports a subset of the ",(0,i.yg)("a",{parentName:"p",href:"https://github.com/json-path/JsonPath/blob/master/README.md"},"JSONPath syntax")," operators, primarily limited to extracting individual values from nested data structures."),(0,i.yg)("table",null,(0,i.yg)("thead",{parentName:"table"},(0,i.yg)("tr",{parentName:"thead"},(0,i.yg)("th",{parentName:"tr",align:null},"Operator"),(0,i.yg)("th",{parentName:"tr",align:null},"Description"))),(0,i.yg)("tbody",{parentName:"table"},(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"$")),(0,i.yg)("td",{parentName:"tr",align:null},"Root element. All JSONPath expressions start with this operator.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},".<name>")),(0,i.yg)("td",{parentName:"tr",align:null},"Child element in dot notation.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"['<name>']")),(0,i.yg)("td",{parentName:"tr",align:null},"Child element in bracket notation.")),(0,i.yg)("tr",{parentName:"tbody"},(0,i.yg)("td",{parentName:"tr",align:null},(0,i.yg)("inlineCode",{parentName:"td"},"[<number>]")),(0,i.yg)("td",{parentName:"tr",align:null},"Array index.")))),(0,i.yg)("p",null,"Consider the following example input JSON:"),(0,i.yg)("pre",null,(0,i.yg)("code",{parentName:"pre",className:"language-json"},'{"x":1, "y":[1, 2, 3]}\n')),(0,i.yg)("ul",null,(0,i.yg)("li",{parentName:"ul"},"To return the entire JSON object:",(0,i.yg)("br",null),(0,i.yg)("inlineCode",{parentName:"li"},"$")," -> ",(0,i.yg)("inlineCode",{parentName:"li"},'{"x":1, "y":[1, 2, 3]}')),(0,i.yg)("li",{parentName:"ul"},'To return the value of the key "x":',(0,i.yg)("br",null),(0,i.yg)("inlineCode",{parentName:"li"},"$.x")," -> ",(0,i.yg)("inlineCode",{parentName:"li"},"1")),(0,i.yg)("li",{parentName:"ul"},"For a key that contains an array, to return the entire array:",(0,i.yg)("br",null),(0,i.yg)("inlineCode",{parentName:"li"},"$['y']")," -> ",(0,i.yg)("inlineCode",{parentName:"li"},"[1, 2, 3]")),(0,i.yg)("li",{parentName:"ul"},"For a key that contains an array, to return an item in the array:",(0,i.yg)("br",null),(0,i.yg)("inlineCode",{parentName:"li"},"$.y[1]")," -> ",(0,i.yg)("inlineCode",{parentName:"li"},"2"))))}m.isMDXComponent=!0}}]); |