Core DQL syntax, pitfalls, query patterns, and query optimization. Load to write, build, fix, or OPTIMIZE a DQL query — prevents syntax errors and makes queries faster, more efficient, and cheaper (less data scanned = lower query consumption/cost per run). Covers fetch commands, data models, field n

DQL Essentials Skill DQL is a pipeline based query language. Queries chain commands with to filter, transform, and aggregate data. DQL has unique syntax that differs from SQL — load this skill before writing any DQL query. When to Load References Before working on specific tasks, load the relevant reference: Task Required Reading Field names, namespaces, data models, stability levels, query patterns [references/semantic dictionary.md](references/semantic dictionary.md) Query optimization — make a query faster / more efficient / cheaper, reduce consumption & scanned data (filter early, bucket filters, time ranges, field selection, sampling, cardinality) [references/optimization.md](references/optimization.md) Smartscape topology navigation for discovering relationships between entities [references/smartscape topology navigation.md](references/smartscape topology navigation.md) summarize and makeTimeseries patterns (bucketing, calendar months) [references/summarization.md](references/summarization.md) Array and timeseries manipulation ( arrayFilter , collectArray , iterative) [references/iterative expressions.md](references/iterative expressions.md) Conditional logic ( if/else chains), coalesce , string/date helpers [references/useful expressions.md](references/useful expressions.md) in operator (subquery), full @ time alignment unit table [references/operators.md](references/operators.md) matchesValue , matchesPhrase , matchesPattern , in() — string pattern matching, regex, array matching, wildcards, case sensitivity [references/string matching.md](references/string matching.md) DQL Reference Index Use this index to route from a function group (e.g. time functions, conversions) to its detailed spec, or from a function name to its spec file. Description Items [Data Types](references/dql/dql data types.md) array , binary , boolean , double , duration , long , record , string , timeframe , timestamp , uid [Parameter Value Types](references/dql/dql parameter value types.md) bucket , dataObject , dplPattern , entityAttribute , entitySelector , entityType , enum , executionBlock , expressionTimeseriesAggregation , expressionWithConstantValue , expressionWithFieldAccess , fieldPattern , filePattern , identifierForAnyField , identifierForEdgeType , identifierForFieldOnRootLevel , identifierForNodeType , joinCondition , jsonPath , metricKey , metricTimeseriesAggregation , namelessDplPattern , nonEmptyExecutionBlock , prefix , primitiveValue , simpleIdentifier , tabularFileExisting , tabularFileNew , url [Commands](references/dql/dql commands.md) append , data , dedup , describe , expand , fetch , fields , fieldsAdd , fieldsFlatten , fieldsKeep , fieldsRemove , fieldsRename , fieldsSnapshot , fieldsSummary , filter , filterOut , join , joinNested , limit , load , lookup , makeTimeseries , metrics , parse , search , smartscapeEdges , smartscapeNodes , sort , summarize , timeseries , traverse [Functions — Aggregation](references/dql/dql functions aggregation.md) avg , collectArray , collectDistinct , correlation , count , countDistinct , countDistinctApprox , countDistinctExact , countIf , max , median , min , percentRank , percentile , percentileFromSamples , percentiles , stddev , sum , takeAny , takeFirst , takeLast , takeMax , takeMin , variance [Functions — Array](references/dql/dql functions array.md) arrayAvg , arrayConcat , arrayCumulativeSum , arrayDelta , arrayDiff , arrayDistinct , arrayFirst , arrayFlatten , arrayIndexOf , arrayLast , arrayLastIndexOf , arrayMax , arrayMedian , arrayMin , arrayMovingAvg , arrayMovingMax , arrayMovingMin , arrayMovingSum , arrayPercentile , arrayRemoveNulls , arrayReverse , arraySize , arraySlice , arraySort , arraySum , arrayToString , vectorCosineDistance , vectorInnerProductDistance , vectorL1Distance , vectorL2Distance [Functions — Bitwise](references/dql/dql functions bitwise.md) bitwiseAnd , bitwiseCountOnes , bitwiseNot , bitwiseOr , bitwiseShiftLeft , bitwiseShiftRight , bitwiseXor [Functions — Boolean](references/dql/dql functions boolean.md) exists , in , isFalseOrNull , isNotNull , isNull , isTrueOrNull , isUid128 , isUid64 , isUuid [Functions — Cast](references/dql/dql functions cast.md) asArray , asBinary , asBoolean , asDouble , asDuration , asIp , asLong , asNumber , asRecord , asSmartscapeId , asString , asTimeframe , asTimestamp , asUid [Functions — Constant](references/dql/dql functions constant.md) e , pi [Functions — Conversion](references/dql/dql functions conversion.md) toArray , toBoolean , toDouble , toDuration , toIp , toLong , toSmartscapeId , toString , toTimeframe , toTimestamp , toUid , toVariant [Functions — Create](references/dql/dql functions create.md) array , duration , ip , record , smartscapeId , timeframe , timestamp , timestampFromUnixMillis , timestampFromUnixNanos , timestampFromUnixSeconds , uid128 , uid64 , uuid [Functions — Cryptographic](references/dql/dql functions cryptographic.md) hashCrc32 , hashMd5 , hashSha1 , hashSha256 , hashSha512 , hashXxHash32 , hashXxHash64 [Functions — Entities](references/dql/dql functions entities.md) classicEntitySelector , entityAttr , entityName [Functions — Time series aggregation for expressions](references/dql/dql functions expression timeseries.md) avg , count , countDistinct , countDistinctApprox , countDistinctExact , countIf , end , max , median , min , percentRank , percentile , percentileFromSamples , start , sum [Functions — Flow](references/dql/dql functions flow.md) coalesce , if [Functions — General](references/dql/dql functions general.md) jsonField , jsonPath , lookup , parse , parseAll , type [Functions — Get](references/dql/dql functions get.md) arrayElement , getEnd , getHighBits , getLowBits , getStart [Functions — Iterative](references/dql/dql functions iterative.md) iAny , iCollectArray , iIndex [Functions — Mathematical](references/dql/dql functions mathematical.md) abs , acos , asin , atan , atan2 , bin , cbrt , ceil , cos , cosh , degreeToRadian , exp , floor , hexStringToNumber , hypotenuse , log , log10 , log1p , numberToHexString , power , radianToDegree , random , range , round , signum , sin , sinh , sqrt , tan , tanh [Functions — Network](references/dql/dql functions network.md) ipIn , ipIsLinkLocal , ipIsLoopback , ipIsPrivate , ipIsPublic , ipMask , isIp , isIpV4 , isIpV6 [Functions — Smartscape](references/dql/dql functions smartscape.md) getNodeField , getNodeName [Functions — String](references/dql/dql functions string.md) concat , contains , decodeBase16ToBinary , decodeBase16ToString , decodeBase64ToBinary , decodeBase64ToString , decodeUrl , encodeBase16 , encodeBase64 , encodeUrl , endsWith , escape , getCharacter , indexOf , lastIndexOf , levenshteinDistance , like , lower , matchesPattern , matchesPhrase , matchesRegex , matchesValue , punctuation , replacePattern , replaceString , splitByPattern , splitString , startsWith , stringLength , substring , trim , unescape , unescapeHtml , upper [Functions — Time](references/dql/dql functions time.md) formatTimestamp , getDayOfMonth , getDayOfWeek , getDayOfYear , getHour , getMinute , getMonth , getSecond , getWeekOfYear , getYear , now , unixMillisFromTimestamp , unixNanosFromTimestamp , unixSecondsFromTimestamp [Functions — Time series aggregation for metrics](references/dql/dql functions timeseries.md) avg , count , countDistinct , end , max , median , min , percentRank , percentile , start , sum Syntax Pitfalls ❌ Wrong ✅ Right Issue filter field in ["a", "b"] filter in(field, {"a", "b"}) [ and ] wrap sub queries in DQL but do not wrap static array literals. Use {} or array() for static values. filter: { in(field, [sub query]) } (e.g. in timeseries filter: ) filter: { field in [sub query] } in() does not accept execution blocks as arguments. When the right hand side is a sub query (execution block), use the in operator: field in [execution block] . by: severity, status by: {severity, status} List of fields must be grouped by curly braces in by: clauses ( summarize , makeTimeseries , etc.). contains(toLowercase(field), "err") contains(field, "err", false) Don't wrap in lower() for case insensitive matching. contains() has a built in third positional caseSensitive parameter (default true ). filter name == " serv 9 " filter matchesValue(name, " serv ") and matchesValue(name, " 9 ") == does not support wildcards. matchesValue() supports wildcards but only at the beginning and/or end of the pattern—split mid string wildcard intent into multiple calls combined with and . matchesValue(field, "prod") on string field contains(field, "prod") Without wildcards, matchesValue() performs an exact (case insensitive) match — it will not find "production" . Use contains() for substring matching (or matchesValue(field, " prod ") for wildcard matching). iAny(matchesValue(arr[], "x") OR matchesValue(arr[], "y")) matchesValue(arr, {"x", "y"}) matchesValue accepts an array field in the first param and an array literal {} in the second — no iAny or [] needed. The same applies when consolidating multiple contains(f, x) OR contains(f, y) on the same field: use matchesValue(f, {" x ", " y "}) . iAny(matchesPhrase(arr[], "phrase")) matchesPhrase(arr, "phrase") matchesPhrase iterates array fields natively — drop iAny( and [] . Note: the second parameter must be a static string; matchesPhrase(f, array("a","b")[]) is a runtime error. contains(field, "pip") on a short or common token matchesPhrase(field, "pip") contains is a pure substring match — "pip" also fires on "pipenv" , "gripping" . matchesPhrase tokenizes the string and matches whole words only, giving fewer false positives. iAny(in(lower(arr[]), array("a", "b"))) matchesValue(arr, {"a", "b"}, caseSensitive: false) matchesValue is case insensitive by default — no lower() , in() , or iAny wrapper needed. caseSensitive: false shown explicitly here only to mirror the intent of the lower() it replaces. iAny(f1[] == "a" AND f2[] == "b") iterating two separate arrays in(f1, "a") AND in(f2, "b") Multi array iAny is pairwise , not a cross product: element i of f1[] is tested against element i of f2[] . If the arrays differ in length the result is null . Use independent in() checks instead. See [references/iterative expressions.md](references/iterative expressions.md). toLowercase(field) lower(field) The function is lower() , not toLowercase() . Only type casting functions use the to prefix ( toString() , toLong() , etc.). arrayAvg(field[]) or arraySum(field[]) arrayAvg(field) or field[] field[] = element wise iterative expression (array→array); arrayAvg(field) = collapse to scalar (array→single value). Never mix both — arrayAvg(field[]) is semantically wrong. my field after lookup or

dt-dql-essentials