Агрегация в MongoDB. Агрегатные выражения метода aggregation

ЛЕКЦИЯ № 5 4.5 Агрегация 1 4.5.1 Агрегатные выражения 1 4.5.2 Операторы агрегации 17 6.1. Агрегация Агрегирование в общем смысле — это объединение нескольких элементов в единое целое. Агрегация в MongoDB — это процесс объединения при выборке результатов выполнения различных методов. Главная сложность в выборке данных – это работа с массивами, подколлекциями и поддокументами, содержащимися внутри каких-то отдельных элементов (документов и коллекций). Осуществлять поиск и выборку по ним можно и с помощью метода find, но детализировать, объединять, составлять по ним отчеты непосредственно при выборке нельзя. Метод aggregate позволяет осуществлять агрегацию данных вне зависимости от уровня вложенности. Агрегация данных осуществляется с помощью агрегатных выражений и операторов. Агрегатные выражения выполняют действия над документами и поддокументами коллекций/подколлекций. Агрегатные операторы выполняют операции над значениями полей документов/поддокументов. Помимо агрегатных операторов метод $aggregate поддерживает операторы метода find (табл. 8 – 10). 4.5.1 Агрегатные выражения В MongoDB реализован инструмент для выполнения цепочки операторов обработки документов коллекции — фреймворк агрегирования данных. На вход агрегирования поступают исходные документы коллекции, для которых последовательно выполняется цепочка операций. Документы на выходе одной операции поступают на вход следующей. Результат последней операции является результатом всей обработки. Конвейерную обработку выполняет ме1 тод aggregate. С помощью метода aggregate можно решать следующие задачи: 1) выполнение арифметических операций со значениями полей; 2) вычисление минимального значения поля; 3) вычисление максимально значения поля; 4) задание полю псевдонима; 5) конкатенация (соединение) значений всех строк заданного поля и другие операции со строками; 6) вывод данных из нескольких коллекций; 7) выполнение операций над датами; 8) группировка данных; 9) подсчет количества документов в коллекции/подколлекции; 10) выполнение операций с массивами. Метод aggregate нужно использовать для работы над значениями полей документов подколлекции. Обобщенный синтаксис метода aggregate имеет следующий вид: db.<Имя_коллекции>.aggregate ([<Агрегатное_выражение>,..]); <Агрегатное_выражение> — выполняемое действие и аргументы — атрибуты обрабатываемых входных документов, записанные в форме JSONобъекта: {<Оператор_агрегации>: [<Оператор_агрегации1: {Имя_поля1:Значение_поля1>}, <Оператор_агрегации2: {Имя_поля2:Значение_поля2>}…]} или для одиночного аргумента операция агрегации имеет вид: Оператор_агрегации:{Имя_поля:Значение_поля>} Примечание. При выполнении агрегации метод aggregate не может использоваться без агрегатных выражений. При выполнении агрегации необходимо указать хотя бы одно агрегатное выражение. Метод aggregate возвращает единственный документ с атрибутом result, 2 содержащий массив документов, элементы которого являются результатом агрегатной обработки исходных документов. Метод aggregate поддерживает использование в запросах выражений, аналогов некоторых встроенных функций SQL. Список доступных агрегатных выражений представлен в таблице 12. Таблица 12 Агрегатные выражения метода aggregation Имя Описание Синтаксис Описание синтаксиса агрегатного выражения Агрегатные выражения для добавления полей $addFields При выводе db.Имя_коллекции.aggreg Имя_поля – результата ate( [{$addFields: пользовательское имя выполнения {Имя_поля_1: поля, присваемое запроса {оператор_агрегации_1: вычисляемому полю; добавляет новые {"$Имя_поля_документа"} поля в },…,{Имя_поля_N: документы/подд {оператор_агрегации_N: окументы коллекции/подко оператор_агрегации – оператор, который осуществляет {"$Имя_поля_документа"} преобразование полей }}] ) ллекции БД и документов коллекции БД заменяет значения существующих полей $project При выводе db.Имя_коллекции.aggreg имя_агрегатного_выражен результата ate([{$project ия – название этапа выполнения {Имя_поля_1: агрегации; запроса {оператор_агрегации_1: Имя_поля – 3 изменяет {"$Имя_поля_документа"} пользовательское имя каждый },…,{Имя_поля_N: поля, присваемое документ, {оператор_агрегации_N: вычисляемому полю например, {"$Имя_поля_документа"} добавляя новые }}] ) поля или удаляя существующие поля Агрегатные выражения для группировки и объединения данных $sample Случайным db.Имя_коллекции.aggreg В параметр size образом ate([ { $sample: { size: заносится число выбирает указанное Количество_документов } выводимых документов } ]) количество документов из входных данных $group Выполняет db.Имя_коллекции.aggreg _id – содержит имя и группировку ate([{$group : {_id : значение поля, по документов по {Имя_поля:Значение_пол которому полю заданному я},{Имя_поля_1: осуществляется в запросе {оператор_агрегации_1: группировка, если в {"$Имя_поля_документа"} значении _id указано },…,{Имя_поля_N: null, то группировка не {оператор_агрегации_N: происходит, операторы {"$Имя_поля_документа"} агрегации применяются }}] ) ко всем документам коллекции БД $lookup Выполняет db.Имя_коллекции.aggreg $lookup выполняет объединение ate([{$lookup: {from: проверку на равенство 4 данных из нескольких коллекций "Имя_родительской_колл localField и foreignField из екции",localField: документов коллекции. "Поле_родительской_кол Если входной документ лекции",foreignField: не содержит localField, "Имя_дочерней_коллекци полю присваивается null и",as:"Поле_дочерней_ко ллекции"}}]) Агрегатные выражения для подсчета документов в коллекции/подколлекции $bucketAuto Выводит db.Имя_коллекции.aggreg groupBy – параметр, минимальное и ate([{$bucketAuto: содержащий поле, среди максимальное {groupBy: значений которого будут значение поля "$Имя_поля",buckets: найдены максимальное заданного в Целое_число}}]) и минимальное запросе, а также значения; количество buckets – параметр, документов позволяющий задать коллекции/подко количество диапазонов, ллекции в которых ищутся максимальное и минимальное значения поля $count Выполняет db.Имя_коллекции.aggreg Имя_вложенного_ подсчет ate([{$unwind:"$Имя_влож объекта – имя количества енного_объекта"}, подколлекции или документов во {$count:" массива, в котором вложенных Имя_вложенного_ осуществляется подсчет объектах, объекта "}]) документов; используется $count – возвращает совместно с массив элементов, 5 оператором указанных в запросе $unwid Агрегатные выражения для разделения данных по этапам агрегации $facet Позволяет db.Имя_коллекции.aggreg имя_агрегатного_выражен выполнить ate([{$facet:{Имя_поля_1: ия – название этапа несколько {имя_выражения_агрегац агрегации; агрегатных ии_1: Имя_поля – выражений в {"$Имя_поля_документа"} пользовательское имя рамках одного },…,{Имя_поля_N: поля, присваемое этапа {имя_выражения_агрегац вычисляемому полю ии: {"$Имя_поля_документа"} }}]) $match Фильтрует db.Имя_коллекции.aggreg В соответствие с документы ate([{$match : указанными {Имя_поля:Значение_пол Имя_поля:Значение_ я},{Имя_поля_1: поля документы {оператор_агрегации_1: фильтруются перед {"$Имя_поля_документа"} переходом на },…,{Имя_поля_N: следующий этап {оператор_агрегации_N: агрегации {"$Имя_поля_документа"} }}] ) $unwind Деконструкция db.Имя_коллекции.aggreg path используется для поля массива из входных ate( [ { $unwind: { path: указания пути к полю; "$полное_имя_поля",pres includeArrayIndex – имя документов. erveNullAndEmptyArrays: нового поля для Разбивает true|false } } хранения индекса массив на поля ]) массива элемента. Имя 6 документов в не может начинаться со формате ключ- знака доллара $. значение. Для preserveNullAndEmptyArrays каждого – если путь является входного недействительным, документа отсутствует или пустой выводит n массив, $unwid выдает документов, документ. Если false, где n – $unwid не выводит количество документ элементов массива и может быть нулем для пустого массива Агрегатные выражения позволяют выполнять сложные запросы благодаря их сочетанию. Пример синтаксиса взаимодействия агрегатных выражений: db.Имя_коллекции.aggregate([ <{$unwind:{<{filtration}>}}> <,{$match: { <{ filtration}>}}> <,{$project: { <{ filtration}>}}> <,{$group: {<{ filtration}>}}> <, { $sort: {Имя_поля: значение сортировки}}> …]) или db.runCommand( { aggregate: "Имя_коллекции", pipeline: [< {$project : {Имя_поля:"$Значение_поля"}}><, {$unwind:"$Имя_поля"}><, {$match: {"Имя_поля":Значение_поля, "Имя_поля":Значение_поля}}> ], cursor: { } } ) где 7 filtration – необязательный параметр, включающий в себя условия выборки; значение сортировки – 1 и -1 для сортировки по возрастанию и убыванию. Примечание. Порядок следования агрегатных выражений является произвольным. Рассмотренное взаимодействие выражений метода aggregate для примера представлено в следующем запросе: db.ABONENT.aggregate([{$match:{"STREET.id":3}},{$count:"ABONENT"}]) Рис. 4.92. Агрегация данных с помощью агрегатных выражений Выражение $match из всех документов коллекции ABONENT отбирает 8 документы, поле «STREET.id» которых равняется 3. Затем с помощью выражения $count подсчитывается количество документов, отобранных выражением $match. Количество абонентов, проживающих на улице с номером 3, можно также с помощью метода count и совместного использования методов count и find. db.ABONENT.count({"STREET.id":3}) db.ABONENT.find({"STREET.id":3}).count() Метод count и совместное использование методов count и find возвращают одинаковый результат. Отдельное использование метода count от метода find добавлено в версии MongoDB 3.6. Подсчет количества документов с помощью агрегации позволяет кроме количества документов вывести значения других полей документов. Агрегация позволяет строить цепочку для обработки данных, перечисляя выражения. Благодаря таким цепочкам выражений и операторов агрегация позволяет возвращать обработанные поддокументы и подколлекции после проведения вычислений над ними и значительно улучшать производительность обработки данных. Далее рассмотрены примеры последовательного построения агрегации с использованием нескольких агрегатных выражений. 1. Использование выражения $match Например, чтобы вывести информацию об абонентах, у которых нет номера телефона, нужно выполнить запрос: db.ABONENT.aggregate([{$match:{"Phone":{$eq:null}}},{$project:{Fio:1}}]) Результат выполнения запроса: /* 1 */ { "_id" : "136159", "Fio" : "Свирина З.А." 9 } /* 2 */ { "_id" : "136160", "Fio" : "Шмаков С.В." } В ходе выполнения запроса сначала из коллекции «ABONENT», с помощью выражения $match, отобраны все документы, у которых есть поле Phone не равно null. Затем выражение $project позволяет задать поля для вывода в результате выполнения запроса, выполняет горизонтальную проекцию. Например, чтобы вывести по каждой услуге значения оплат и номера месяцев, за которые они произведены, нужно выполнить запрос: db.SERVICES.aggregate([{$match:{"PAYSUMMA.PayMonth":{$ne:null}}},{$project: {"PAYSUMMA.PaySum":1,"PAYSUMMA.PayMonth":1}}]) или db.runCommand({aggregate:"SERVICES",pipeline:[{$match: {"PAYSUMMA.PayMonth":{$ne:null}}},{$project: {"PAYSUMMA.PaySum":1,"PAYSUMMA.PayMonth":1}}],cursor:{}}) Здесь для каждой услуги в начале с помощью выражения $match отбираются все документы, в которых указан месяц оплаты, а затем выражение $project выводит значения только указанных в нем полей. В результате выведены значения полей PaySum и PayMonth документов подколлекции PAYSUMMA коллекции SERVICES (рис. 4.93). 10 Рис. 4.93. Фрагмент результата выполнения запроса 2. Использование выражения $unwind Выражение $unwind используется для развертывания подколлекции. После проведения развертывания, можно обращаться к документам подколлекции и выполнять операции над ними. До проведения развертывания все действия относятся к документам коллекции. Например, чтобы подсчитать количество заявок, нужно выполнить запрос: db.DISREPAIR.aggregate([{$unwind:"$REQUEST"},{$count:"REQUEST"}]) Выражение $unwind разворачивает подколлекцию REQUEST, затем выражение $count подсчитывает количество документов в подколлекции. В результате выполнения запроса выведено количество документов подколлекции REQUEST коллекции DISREPAIR: 21 3. Совместное использование выражений $match и $unwind Например, чтобы вывести названия неисправностей заявок с номерами меньше 8 и номера слесарей, которым назначены найденные заявки, с номером больше 2, нужно выполнить запрос: 11 db.DISREPAIR.aggregate([{$unwind:"$REQUEST"},{$match:{"REQUEST._id": {$lt:8}}},{$match:{"REQUEST.EXECUTOR.id":{$gt:2}}}]) Выражение $unwind разворачивает подколлекцию REQUEST, затем выражение $match отбирает все документы, в которых _id документов подколлекции REQUEST меньше 8 и поле «EXECUTOR.id» имеет значение больше 2. В результате выполнения запроса выведены документы подколлекции REQUEST со значением поля _id меньше 8 и документы коллекции EXECUTOR со значением _id больше 2. /* 1 */ { "_id" : 1, "FailureNM" : "Засорилась водогрейная колонка","REQUEST" : {"_id" : 2, "ABONENT" :{"ref":"ABONENT","id": "115705"},"IncomingDate" : ISODate("201408-06T21:00:00.000Z"),"ExecutionDate" : ISODate("2014-08- 11T21:00:00.000Z"),"Executed" : true, "EXECUTOR" : {"ref" : "EXECUTOR","id" : 3} } } /* 2 */ {"_id" : 1, "FailureNM" : "Засорилась водогрейная колонка","REQUEST" : {"_id" : 5, "ABONENT" :{"ref":"ABONENT","id": "080270"},"IncomingDate" : ISODate("201412-30T21:00:00.000Z"),"Executed" : false, "EXECUTOR" : {"ref" : "EXECUTOR","id" : 4}} } /* 3 */ {"_id" : 2, FailureNM" : "Не горит АГВ","REQUEST" : {"_id" : 7, "ABONENT" : {"ref":"ABONENT","id": "080047"},"IncomingDate" 19T21:00:00.000Z"),"ExecutionDate" : : ISODate("2015-10ISODate("2015-10- 23T21:00:00.000Z"),"Executed" : true, "EXECUTOR" : {"ref" : "EXECUTOR","id" : 3}} } 4. Использование выражения $project Если требуется выбрать определенные документы из коллекции, то для указания значений или имен полей, которые нужно выбрать, используется 12 агрегатное выражение $project. Если для каждой услуги требуется вывести ее номер и номер лицевого счета каждого факта оплаты, то это можно получить так: db.SERVICES.aggregate([{$project:{"PAYSUMMA.ABONENT":1}}]) В результате выполнения запроса выведены только значения полей «PAYSUMMA.ABONENT.id» и «PAYSUMMA.ABONENT.ref» документов подколлекции «PAYSUMMA» коллекции «SERVICES» (рис. 4.94). Рис. 4.94. Результат выполнения запроса с выражением $project Для выбора неисправностей, заявки по которым были зарегистрированы позднее 1 октября 2016 года, нужно выполнить запрос: db.DISREPAIR.aggregate([{$project:{_id:1,FailureNM:1,Date:{$gt: [ "$REQUEST.IncomingDate", "01.10.2016" ] }} }]) В результате выполнения запроса выражение $project выводит значения полей _id и FailureNM, а также поле с псевдонимом Date, содержащее 13 результат сравнения даты принятия заявки и даты 1 октября 2016. Если дата выполнения всех заявок по неисправности больше 1 октября 2016, то выводится значение true, иначе false. В результате выполнения запроса в поле Date выведется значение true, если дата принятия заявки больше указанной в запросе, и false, если меньше (рис. 4.95). Рис. 4.95. Результат выполнения запроса 5. Использование агрегатного выражения $group Например, чтобы вывести по каждой услуге суммы значений оплат, надо выполнить запрос (результат на рис. 4.96): db.SERVICES.aggregate([ {$unwind : "$PAYSUMMA"},{$group:{ _id:"$_id" , PaySum: {$sum: "$PAYSUMMA.PaySum"}}}]) Рис. 4.96. Взаимодействие операторов метода aggregation 6. Использование агрегатного выражения $sort 14 Например, для вывода по каждой услуге сумм значений оплат, отсортированных по убыванию, нужно выполнить запрос (результат на рис. 4.97): db.SERVICES.aggregate([{$unwind:"$PAYSUMMA"}, {$group : { _id:"$ServiceNM",PaySum: {$sum: "$PAYSUMMA.PaySum"}}},{$sort : {PaySum : -1}} ] ) Результат выполнения запроса: { "_id" : "Водоснабжение", "PaySum" : 6229.21 } { "_id" : "Теплоснабжение", "PaySum" : 3977.55 } { "_id" : "Электроснабжение", "PaySum" : 1275.1 } { "_id" : "Газоснабжение", "PaySum" : 926.92 } или db.runCommand({aggregate:"SERVICES",pipeline:[{$unwind:"$PAYSUMMA"}, {$group : { _id:"$ServiceNM" ,PaySum: {$sum: "$PAYSUMMA.PaySum"}}},{$sort : {PaySum : -1}} ], cursor: { }} ) 15 Рис. 4.97. Результат выполнения запроса 7. Использование агрегатного выражения $lookup Агрегация в MongoDB также используется для объединения данных при выводе результата запроса из двух коллекций. Агрегатное выражение $lookup метода aggregate используется для объединения данных из нескольких коллекций. Аналогом этого метода является LEFT JOIN (левое соединение) в реляционных СУБД. Например, чтобы всю вывести информацию обо всех абонентах вместе с названиями улиц, на которых они проживают, нужно выполнить запрос: db.ABONENT.aggregate([{ $lookup: { from: "STREET", localField: "STREET.id", foreignField: "_id", as: "STREET.id"}}]) или db.runCommand({ aggregate:"ABONENT", pipeline:[ {$lookup:{ 16 from:"STREET", localField:"STREET.id", foreignField:"_id", as:"STREET.id"}} ], cursor:{} } ) В результате выполнения запроса в поле «STREET.id» выведены данные из коллекции STREET, а именно номер улицы и ее название (рис. 4.98). Рис. 4.98. Результат выполнения выражения $lookup Например, чтобы найти всю информацию об абонентах с лицевыми счетами 015527, 136159 и о поданных ими заявках, применяется запрос: db.DISREPAIR.aggregate({$match: {"REQUEST.ABONENT.id":"015527","REQUEST.ABONENT.id":"136159"}},{$lookup: {from:"ABONENT",localField:"REQUEST.ABONENT.id", foreignField:"_id",as:"ABONENTS"}}) При выводе результата запроса в коллекцию «DISREPAIR» добавлена 17 подколлекция «ABONENTS», содержащая документы коллекции «ABONENT» со значением поля _id равным 015527 и 136159 (рис. 4.99). Рис. 4.99. Результат выполнения запроса 4.5.2 Операторы агрегации Метод aggregate помимо операторов, доступных для метода find, поддерживает следующие пять видов операторов агрегации: 1) скалярные операторы; 2) арифметические операторы; 3) операторы для массивов; 4) операторы управления документами и коллекциями; 5) условные операторы. Скалярные операторы (их еще называют однострочными) обрабатывают одиночное значение и возвращают также одно значение. Скалярные операторы бывают следующих категорий: 1) строковые операторы, которые выполняют определенные действия над строками и возвращают строковые или числовые значения. В MongoDB такие операторы представлены операторами: $concat, $split, $indexOfBytes, $indexOfCP, $strLenBytes, $substr, $substrBytes, $substrCP, $strcasecmp, $toLower, $toUpper; 18 2) числовые операторы, которые возвращают числовые значения на основании заданных в аргументе значений того же типа. В MongoDB такие операторы представлены операторами: $abs, $add, $ceil, $divide, $exp, $floor, $ln, $log, $log10, $mod, $multiply, $pow, $trunc, $sqrt, $subtract; 3) операторы времени и даты, которые выполняют различные действия над входными значениями времени и даты и возвращают строковое, числовое значения или значение в формате даты и времени. В MongoDB такие операторы представлены операторами: $dayOfYear, $dayOfMonth, $dayOfWeek, $year, $month, $week, $hour, $minute, $second, $millisecond; 4) арифметические операторы, которые используются для получения обобщающих значений. Они, в отличие от скалярных функций, оперируют значениями полей множества документов. К агрегатным операторам относятся такие операторы, как: $sum (итог), $max и $min (наибольшее и наименьшее значения) и $avg (среднее значение). Операторы для массивов используются при обработке значений, заданных в массиве элементов. В MongoDB такие операторы представлены операторами: $arrayElemAt, $arrayToObject, $concatArrays, $filter и другими. Операторы массивов в чем-то похожи на строковые операторы, например, $concatArrays и $concat, однако $concatArrays объединяет элементы не из множества значений строк, а из элементов, заданных в массиве. В качестве операторов управления документами и коллекциями используются операторы: $collStats, $project, $match и другие. Возвращаемый этими операторами результат меняется в зависимости от значения, которое обрабатывается оператором. Условные операторы предназначены для статистического анализа данных в готовой выборке. Это довольно мощный и удобный инструмент, который присутствует во многих СУРаБД. В MongoDB такие операторы представлены операторами: $cond, $ifNull, $switch и другими. 19 В версии СУРаБД 4.0.0 добавлены новые агрегатные операторы для преобразования типов данных на стороне СУБД: универсальный оператор $convert для конвертации в любой указанный тип и привязанные к конкретным типам операторы $toBool, $toDate, $toDecimal, $toDouble, $toInt, $toLong, $toObjectId и $toString. Строковые операторы Строковые операторы (таблица 13) используют в качестве аргумента строку символов, а в качестве результата возвращают также символьную строку или числовое значение. Таблица 13 Строковые операторы Выражение Описание Синтаксис Описание синтаксиса $concat Конкатенация db.Имя_коллекции. В результате строк, операция aggregate([ { $project: запроса при выводе склеивания {Новое_имя_поля: документов строк в одну { $concat: [ "$Имя_поля", коллекции поля "выражение_пользователя", будут соединены в "$Имя_поля_2" ] } } } ]) одно поле, а между ними будет располагаться соединительная строка, заданная пользователем $split Разделяет db.Имя_коллекции. В результате строку на aggregate([ выполнения подстроки. { $project : { Новое_имя_поля: запроса значение Возвращает { $split: ["$Имя_поля", ", "] }} } ]) поля будет 20 массив разделено на подстрок. Если подстроки, по разделитель не указанному найден в разделителю, в строке, примере возвращает разделителем массив, является запятая содержащий исходную строку $indexOfBytes Выполняет db.Имя_коллекции. Данная операция поиск aggregate( использует подстроки и [{$project:{Новое_имя_поля: оператор возвращает { $indexOfBytes: $indexOfBytes для UTF-8 код [ "$Имя_поля", извлечения вхождения "Значение_поля" ] }}}]) индексов, по подстроки. которым находится Если подстрока указанное значение не найдена, поля возвращает -1 $indexOfCP Выполняет db.Имя_коллекции. Операция поиск начала aggregate([ использует подстроки и { $project: { оператор возвращает Новое_имя_поля: $indexOfCP для UTF-8 код {$indexOfCP: [ "$Имя_поля", возврата индекса первого "Значение_поля" ]} начальной точки, в вхождения }}]) которой значение подстроки. поля находится в 21 $toUpper Если подстрока значении поля не найдена, документа с возвращает -1 указанным именем Преобразует db.Имя_коллекции. В результате строку в aggregate([ выполнения верхний {$project:{ запроса все регистр. Имя_поля: { $toUpper: значения Принимает "$Имя_поля" }}}}]) указанного поля одно будут написаны выражение заглавными аргумента. буквами Применяется только к латинским буквам $toLower Преобразует db.Имя_коллекции. В результате строку в aggregate([ выполнения нижний {$project:{ запроса все регистр. Имя_поля: { $toLower: значения Принимает "$Имя_поля" }}}}]) указанного поля одно документов выражение коллекции БД аргумента. будут написаны Применяется строчными только к буквами латинским буквам $strLenBytes Возвращает db.Имя_коллекции. Данная операция 22 число aggregate([{ использует закодированны $project: {" ": 1, оператор х байтов UTF-8 "Новое_имя_поля": $strLenBytes для в строке {$strLenBytes:"$Имя_ вычисления длины поля" }}}]) каждого имени поля документа $substr Возвращает db.Имя_коллекции. Если ($substrBytes строку или aggregate( [ является $substrCP, подстроку. {$project: {_id: 1, отрицательным используются Если длина Имя_поля: { $substr: [ , числом, $substr для вывода указывается , ] }}]) возвращает пустую строки по отрицательным строку "". заданным значением, то Если байтам и возвращается является кодировке пустая строка. отрицательным US-ASCII) Под каждую числом, $substr букву возвращает кириллицы подстроку, с отводится по 2 указанной позиции символа длины длины строки и включает в себя остаток строки $strcasecmp Сравнивает db.Имя_коллекции.aggregate([{ В результате строки и $project:{Имя _поля: выполнения возвращает: { $strcasecmp: [ "$Имя_поля", запроса будут 1, если первая "Значение_поля" ] } } выведены все строка больше }]) документы второй строки; коллекции со 23 0, если две значением строки равны; указанного имени -1, если первая поля (1, 0, -1) строка меньше второй строки $ltrim Удаляет пробе- db.Имя_коллекции.aggregate([{ лы или указан- Если в начале $ltrim: ные символы из {input:string, chars:string}}}]) строки стоит пробел в параметре char он также начала строки $rtrim Удаляет пробе- db.Имя_коллекции.aggregate([{ лы или указан- $rtrim: ные символы из {input:string, chars:string}}}]) указывается конца строки Далее представлены примеры выполнения каждого из операторов обработки строк. Например, для вывода номеров услуг и первых трех символов их названия (результат на рис. 4.100), можно использовать запрос: db.SERVICES.aggregate( [{$project: {ServiceNM: {$substr: [ "$ServiceNM", 0, 6]}}}]) Рис. 100. Результат выполнения запроса Оператор $substr позволяет вывести часть слова или фразы. В рассмотренном выше примере выводится первые три буквы названия услуги, поскольку каждая русская буква занимает два символа длины строки. Например, если требуется вывести лицевой счет и номер телефона абонента с добавлением константы «имеет телефон» и задать псевдоним поля 24 «Информация о телефоне», то используется запрос: db.ABONENT.aggregate([{ $project:{ "Информация о телефоне":{$concat:[ "$_id", " имеет телефон ", "$Phone" ]}}}]) В результате выполнения запроса для каждого документа коллекции ABONENT выведено поле «Информация о телефоне», в котором выведена строковая константа, заданная пользователем и значение поля Phone (рис. 101). Рис. 101. Результат выполнения запроса с оператором $concat Для вывода номеров лицевых счетов зарегистрированных абонентов с добавлением строковой константы «Абонент зарегистрирован под лицевым счетом» с заданным псевдонимом поля «Account» используется запрос (результат на рис. 4.102): db.ABONENT.aggregate([{ $project:{Account:{$concat:[ " Абонент зарегистрирован под лицевым счетом ", "$_id" ]}, _id:0}}]) 25 Рис. 4.102. Добавление строкой константы Например, для вывода лицевых счетов абонентов и первых двух цифр их номера лицевого счета (результат на рис. 4.103) нужно использовать запрос: db.ABONENT.aggregate( [{$project: {Phone: {$substr: [ "$Phone", 0, 2]}}}]) Рис. 4.103. Результат выполнения запроса с оператором $substr Числовые операторы В основном числовые операторы (таблица 14) представляют собой 26 различные арифметические операции, в результате которых возвращается значение такого же типа, как исходные значения в запросе. Таблица 14 Арифметические операторы Выражение Описание Синтаксис Описание синтаксиса $sum Суммирует значения db.Имя_коллекции. Имя_поля – имя поля (SUM) поля, указанного в aggregate([{$project:{ документов запросе Имя_поля : {$sum : коллекции БД, "$Имя_поля"}}}]) значения которых суммируются $min Получает минимум (MIN) из всех значений db.Имя_коллекции. aggregate([{$project:{ коллекции БД, среди указанного в запросе Имя_поля : {$min : "$Имя_поля"}}}]) поля документов коллекции $max (MAX) значений которого находятся минимальное Получает максимум db.Имя_коллекции. Имя_поля – имя поля aggregate([{$project:{ коллекции, среди указанного в запросе Имя_поля : {$max : значений которого из всех значений поля документов "$Имя_поля"}}}]) коллекции $abs Имя_поля – имя поля находится максимальное Вычисляет абсолютное { $abs: – любое значен-ие всех } числовое значение { $exp: } – целое заданных значений из всех документов в коллекции $exp Возвращает экспоненту числа числовое значение 27 $trunc Выводит целую часть { $trunc: } числового значения – любое числовое значение поля $ceil Возвращает { $ceil: Аргумент возвращает наименьшее целое } null, если значение число, большее или отсутствует, иначе равное указанному $ceil возвращает числу, округляя его ближайшее целое к указанному в запросе значению поля $divide Выражение { $divide: [ Первый аргумент – используется для expression_1, это делимое, второй выполнения expression_2] } аргумент – делитель, операции деления первое значение, между указанными делится на второе значениями полей или числами $ln $floor Вычисляет { $ln: }| $log: Math.E – натуральный [ , Math.E ] представление логарифм значения JavaScript для числа поля документа e (Эйлера) Возвращает { $floor: } Если аргумент наибольшее целое указывает на число, меньшее или значение null или равное указанному указывает на поле, числу, округляя в которое отсутствует, меньшую сторону $floor возвращает значение null $log Вычисляет { $log: [ , – 28 логарифм значения $log10 ] } неотрицательное поля документа число. коллекции по – заданному положительное число основанию больше 1 Рассчитывает { $log10: }| десятичный логарифм $log: [ , 10 ] значения поля – неотрицательное число документа коллекции БД $multiply Оператор умножает { $multiply: Аргументы могут значение поля на [ ,…, иметь любое число или на ]} числовое значение { $mod: Первый аргумент – значение другого поля $mod Оператор делит значение поля документа на число [,…, это дивиденд, второй ]} аргумент – делитель, или на значение первый аргумент, другого поля делится на второй аргумент $pow Вычисляет { $pow: [ , – числовое дисперсию значения ] } значение; – целое поля коллекции БД число $sqrt Вычисляет { $sqrt: Аргумент – квадратный корень } неотрицательное значения поля число коллекции БД 29 $subtract Выполняет операцию { $subtract: Второй аргумент вычитания между [expression_1, вычитается из двумя значениями expression_2]} первого аргумента. полей, числами или Аргументы могут датами быть числами и/или датой. Далее представлены примеры использования арифметических операторов. Например, чтобы найти для каждой услуги максимальное и минимальное значения оплаты (результат на рис. 4.104), можно выполнить следующий запрос: db.SERVICES.aggregate([{$project: {PaySumMax: { $max:"$PAYSUMMA.PaySum"}, PaySumMin: { $min: "$PAYSUMMA.PaySum" }}}]) Рис. 4.104. Результат выполнения запроса с операторами max и min Расчет сумм всех оплат и начислений по каждой услуге можно произвести таким образом: db.SERVICES.aggregate([{$project: {PaySum: { $sum: "$PAYSUMMA.PaySum"}, NachislSum: { $sum: "$NACHISLSUMMA.NachislSum" }}}]) или db.runCommand({aggregate:"SERVICES",pipeline:[ {$project:{PaySum:{$sum:"$PAYSUMMA.PaySum"},NachislSum: {$sum:"$NACHISLSUMMA.NachislSum" }}}],cursor: { }} ) Результат выполнения запроса представлен на рис. 4.105 – рис. 4.106. 30 Рис. 4.105. Результат выполнения запроса или 31 Рис. 4.106 Результат выполнения запроса runCommand Здесь происходит суммирование всех оплат и начислений для каждой услуги. Например, чтобы вывести результат вычисления интервала времени, затраченного на выполнение каждой заявки слесарем под номером 3, надо выполнить запрос: db.DISREPAIR.aggregate([{$unwind:"$REQUEST"},{$match: {"REQUEST.EXECUTOR.id":3}},{$project: {EXECUTOR:"$REQUEST.EXECUTOR",Time:{$subtract: ["$REQUEST.ExecutionDate","$REQUEST.IncomingDate"]} } }]) В результате выполнения запроса будет вычислена и выведена разница между датами принятия и выполнения заявки в миллисекундах: {"_id":1,"EXECUTOR":{"ref":"EXECUTOR","id":3}, "Time":NumberLong(432000000)} {"_id":12,"EXECUTOR":{"ref" : "EXECUTOR","id" : 3}, 32 "Time" : NumberLong(172800000)} {"_id":2,"EXECUTOR" : {"ref" : "EXECUTOR","id" : 3}, "Time" : NumberLong(345600000)} {"_id" : 2, "EXECUTOR" : {"ref" : "EXECUTOR","id" : 3 }, "Time" : NumberLong(0)} Или для расчета в днях: db.DISREPAIR.aggregate([{$unwind:"$REQUEST"},{$match: {"REQUEST.EXECUTOR.id":3}},{$project:{EXECUTOR:"$REQUEST.EXECUTOR",Time: {$divide:[{$subtract:["$REQUEST.ExecutionDate","$REQUEST.IncomingDate"]},86400000]} } }]) С помощью выражения $group можно сгруппировать документы по определенному выражению и вывести на следующий этап документ по каждой отдельной группировке. Например, если необходимо вычислить средние значения начислений за каждую услугу (результат на рис. 4.107 – рис. 4.108), то нужно выполнить запрос: db.SERVICES.aggregate([{ $group : {_id: { $avg: "$NACHISLSUMMA.NachislSum" }}}]) или db.runCommand( {aggregate: "SERVICES",pipeline: [{ $group : {_id: { $avg: "$NACHISLSUMMA.NachislSum" }}} ], cursor: { }} ) Рис. 4.107. Результат выполнения запроса с оператором $group или 33 Рис. 4.108. Результат выполнения запроса runCommand Например, если требуется узнать количество дней, затраченное на выполнение всех заявок каждым исполнителем (результат на рис. 4.109), то используется запрос: db.DISREPAIR.aggregate([{$unwind:"$REQUEST"},{$match:{"REQUEST.EXECUTOR.id": {$ne:null}}},{$group:{_id:"$REQUEST.EXECUTOR.id",Sum:{$sum:{$subtract: ["$REQUEST.ExecutionDate","$REQUEST.IncomingDate"]}}}},{$project: {Hours: {$divide: [ "$Sum", 86400000] }}}]) Рис. 4.109. Результат выполнения запроса В результате выполнения запроса выведены _id исполнителей заявок и количество дней, которое было затрачено на выполнение всех заявок. Значение null выводится, поскольку одной заявке не назначен исполнитель. Операторы даты и времени Операторы даты и времени (таблица 15) выполняют различные действия над входными значениями времени и даты строковое, значения или значение в формате числовое даты и и возвращают времени. 34 Таблица 15 Операторы даты и времени Выражение Описание $dayOfYear Возвращает день года для даты как число от 1 до 366 (високосный год) $dayOfMonth Возвращает день месяца для даты как число от 1 до 31 $dayOfWeek Возвращает день недели для даты в виде числа от 1 (воскресенье) до 7 (суббота) $year Возвращает год для даты в виде числа $month Возвращает месяц для даты в виде числа от 1 (январь) до 12 (декабрь) $week Возвращает номер недели как число между 0 (частичная неделя, предшествующая первому воскресению года) и 53 $hour Возвращает час для даты как число между 0 и 23 $minute Возвращает минуты для даты число от 0 до 59 $second Возвращает секунды для даты число от 0 до 60 $millisecond Возвращает миллисекунды для даты число от 0 до 999 Следует отметить, что даты в запросе указываются в одном из форматов, поддерживаемых MongoDB. Например, чтобы вывести название неисправности с номером 3 и даты выполнения заявок по ее устранению, нужно выполнить запрос: db.DISREPAIR.aggregate([{$match:{_id:3}},{$unwind : "$REQUEST"}, {$project:{FailureNM:"$FailureNM","REQUEST":"$REQUEST.ExecutionDate", _id:0}}) или 35 db.runCommand({aggregate:"DISREPAIR",pipeline:[{$match:{_id:3}}, {$unwind:"$REQUEST"},{$project: {FailureNM:"$FailureNM","REQUEST":"$REQUEST.ExecutionDate",_id:0}}],cursor: { }} ) В результате выполнения запроса будет выведено название неисправности с идентификатором 3 и даты выполнения заявок по ее устранению: /* 1 */ { "FailureNM" : "Течет из водогрейной колонки" } /* 2 */ { "FailureNM" : "Течет из водогрейной колонки", "REQUEST" : ISODate("2018-02-03T21:00:00.000Z") } Например, чтобы получить подробную информацию о датах выполнения заявок, нужно выполнить запрос: db.DISREPAIR.aggregate( [{$unwind:"$REQUEST"}, { $project: { year: { $year: "$REQUEST.ExecutionDate" }, month: { $month: "$REQUEST.ExecutionDate" }, day: { $dayOfMonth: "$REQUEST.ExecutionDate" }, hour: { $hour: "$REQUEST.ExecutionDate" }, minutes: { $minute: "$REQUEST.ExecutionDate" }, seconds: { $second: "$REQUEST.ExecutionDate" }, 36 milliseconds: { $millisecond: "$REQUEST.ExecutionDate" }, dayOfYear: { $dayOfYear: "$REQUEST.ExecutionDate" }, dayOfWeek: { $dayOfWeek: "$REQUEST.ExecutionDate" }, week: { $week: "$REQUEST.ExecutionDate" } } } ] ) Фрагмент результата выполнения запроса: /* 1 */ { "_id" : 1, "year" : 2017, "month" : 1, "day" : 19, "hour" : 21, "minutes" : 0, "seconds" : 0, "milliseconds" : 0, "dayOfYear" : 19, "dayOfWeek" : 5, "week" : 3 } /* 2 */ { "_id" : 1, "year" : 2016, "month" : 9, "day" : 11, 37 "hour" : 21, "minutes" : 0, "seconds" : 0, "milliseconds" : 0, "dayOfYear" : 255, "dayOfWeek" : 1, "week" : 37 } Здесь для значения поля Date документа указанной коллекции будет выведено: год, месяц, число, час, минуты, секунды и миллисекунды выполнения заявки, а также порядковый номер дня выполнения заявки в году, номер дня недели выполнения заявки (начиная от воскресенья 1 и до субботы 7) и порядковый номер недели года. Например, чтобы получить подробную информацию о датах подключения к службе MongoDB, упорядоченных по убыванию, нужно выполнить запрос: db.startup_log.aggregate( [ { $project: { year: { $year: "$startTime" }, month: { $month: "$startTime" }, day: { $dayOfMonth: "$startTime" }, hour: { $hour: "$startTime" }, minutes: { $minute: "$startTime" }, seconds: { $second: "$startTime" }, milliseconds: { $millisecond: "$startTime" }, dayOfYear: { $dayOfYear: "$startTime" }, dayOfWeek: { $dayOfWeek: "$startTime" }, 38 week: { $week: "$startTime" } } }, { $sort:{_id:-1}} ] ) Например, чтобы отобразить номер месяца поступления заявок для неисправности с идентификатором 2, нужно выполнить запрос: db.DISREPAIR.aggregate([{$unwind : "$REQUEST"},{$match: { _id: 2 }},{$project: {month: { $month: "$REQUEST.IncomingDate"}}}]) В результате выполнения запроса выведены все номера месяцев поступления заявок по неисправности с номером 2: {"_id" : 2,"month" : 10} {"_id" : 2,"month" : 8} {"_id" : 2, "month" : 10} {"_id" : 2,"month" : 9} Операторы для работы с массивами Операторы для работы с массивами (таблица 16) используются для обработки значений, заданных в массиве элементов. Таблица 16 Операторы массивов Выражение Описание Синтаксис Описание синтаксиса $setIntersec Выводит db.Имя_массива. Элемент_массива – tion совпавшие aggregate([{$project: элементы массива элементы {Имя_массива: коллекции БД, которые сравниваемых {$setIntersection: сравниваются друг с массивов [ "$Элемент_массива", другом "$Элемент_массива"] }}}]) 39 $setEquals Сравнивает db.Имя_коллекции. элементы_массива1– массивы и aggregate([{$project: элементы массива выводит true, {$setEquals: коллекции БД, которые если они имеют [ "Элемент_массива_1", похожие "…","Элемент_массива_N элементы, "], ["Элемент_массива_2 иначе false "…", сравниваются с элементами_массива2 "Элемент_массива_M"] ]}}] ) $arrayElemA Возвращает { $arrayElemAt: [ , – массив; t элемент по ] } элемент – целое указанному число, если индексу положительное массива значение, то $arrayElemAt возвращает элемент в позиции idx, считая с начала массива, иначе – считая от конца массива. Если индекс превышает границы массива, $arrayElemAt не возвращает никакого результата $concat Объединяет {$concatArrays: – массивы строк Arrays массивы [ ,..., или чисел ]} $filter Выбирает { $filter: { input: , as: as – имя переменной, 40 подмножество , cond: представляющей массива, чтобы } } каждый отдельный вернуть массив элемент входного только с массива. Если имя не элементами, указано, по умолчанию соответствую используется имя щими условию переменной. фильтра cond – выражение, задающее условие для включения элемента в выходной массив. Выражение ссылается на каждый элемент входного массива с помощью переменной, указанной в as $isArray Определяет, { $isArray: Expression (выражение) является ли [ ] } может включать имена операнд полей и системные массивом переменные, объекты, массивы и операторы выражений. Возвращает логическое выражение $indexOf Поиск { $indexOfArray: [ – массив. Array определенного expression>, , , указывает на поле, успешном ] } которое отсутствует, поиске $indexOfArray 41 возвращает возвращает значение индекс в null. массиве Если выражение выбора первого не входит в массив или вхождения. равно null, Если подстрока $indexOfArray не найдена, возвращает ошибку. возвращает -1 – целое число или число, которое может быть представлено целыми числами (например, 2.0), по умолчанию начало строки. – целое число $range Выводит { $range: [ , , – целое число, массив, ] } определяющее начало содержащий последовательности. последовательн – целое число, ость чисел в указывающее верхний соответствии с предел входными последовательности. параметрами и – операторами необязательный параметр. Целое число, указывающее значение приращения $reverse Возвращает { $reverseArray: } массив с возвращает пустой элементами в массив, когда аргумент обратном является пустым порядке массивом. Если аргумент содержит подмассивы, $reverseArray работает только на «верхнем» уровне и не изменяет содержание подмассивов Например, чтобы вывести первый и последний элемент массива AccountCD, нужно выполнить запрос: db.SERVICES.aggregate([ { $project:{ ServiceNM: 1, first: { $arrayElemAt: [ "$AccountCD", 0 ] },last: {$arrayElemAt:["$AccountCD", -1 ] }}}]) В результате выполнения запроса для каждой услуги будет выведен первый и последний элемент массива AccountCD: {"_id" : 2,"first" : "005488", "last" : "126112"} {"_id" : 1,"first" : "005488","last" : "126112"} { "_id" : 3,"first" : "005488","last" : "126112"} {"_id" : 4,"first" : "005488","last" : "126112"} Например, чтобы отобразить элементы массива AccountCD в обратном порядке, нужно выполнить запрос: db.SERVICES.aggregate([ {$project:{ServiceNM: 1, reverseAccount: { $reverseArray: "$AccountCD" }}}]) В результате выполнения запроса элементы массива AccountCD будут выведены в обратном порядке: {"_id" : 2,"ServiceNM" : "Электроснабжение", "reverseAccount" : [ "126112", " 443690", "443069", " 136160", " 136159", "136169", " 080047", " 080613", " 080270", " 015527", " 115705", "005488" ]} 43 Представлен фрагмент результата запроса только для одной услуги. Операторы управления документами и коллекциями Операторы управления документами и коллекциями (таблица 17) возвращают результат, который меняется в зависимости от значения, обрабатываемого оператором. Таблица 17 Операторы управления документами и коллекциями Выражение $collStats $geoNear Описание Возвращает Синтаксис { $collStats: Описание синтаксиса latencyStats добавляет статистику latencyStats: статистику задержек в относительно { histograms: возвращаемый коллекции }, документ Возвращает storageStats: {}}} { $geoNear: { geoNear dist.calculated – документы, options} } вычисляемое поле, основанные на содержащее близости к расстояние; геопространствен dist.location – поле ной точке, местоположения, включая поле содержащее местоположения местоположение, используемое при $first Получает первый db.Имя_коллекции. расчете Имя_поля – имя поля (FIRST) документ из aggregate([{$project:{ документов коллекции исходных Имя_поля : {$first : БД, среди значений документов, "$Имя_поля"}}}]) которых ищется $last содержащих первое, указанное в удовлетворяющее запросе поле Получает условию поиска Имя_поля – имя поля db.Имя_коллекции. 44 последний aggregate([{$project: коллекции БД, из документ из {Имя_поля : {$last : значений которых исходных "$Имя_поля"}}}]) выбирается последнее (LAST) документов, поле содержащих указанное в запросе поле Например, для вывода в разрезе услуг даты первой оплаты, нужно выполнить запрос: db.SERVICES.aggregate([{$unwind:"$PAYSUMMA"},{$group: {_id:"$ServiceNM",Date:{$first:"$PAYSUMMA.PayDate" }}} ]) В результате выполнения запроса для каждого значения поля ServiceNM коллекции SERVICES выведено первое записанное в базу значение поля PayDate документа подколлекции PAYSUMMA (рис. 111). Рис. 4.111. Результат выполнения запроса с оператором $first Например, чтобы вывести в разрезе услуг год первого начисления, нужно выполнить запрос: db.SERVICES.aggregate([{$unwind:"$NACHISLSUMMA"},{$group: {_id:"$ServiceNM",Date:{$last:"$NACHISLSUMMA.NachislYear" }}}]) В результате выполнения запроса для каждого значения поля ServiceNM коллекции SERVICES выведено последнее записанное в базу 45 значение поля NachislYear документа подколлекции NACHISLSUMMA (рис. 112). Рис. 4.112. Результат выполнения запроса с оператором $last Операторы выбора Операторы выбора (таблица 18) предназначены для статистического анализа данных в готовой выборке. Это довольно мощный и удобный инструмент, который присутствует во многих СУБД . Таблица 18 Операторы выбора Выражение Описание Синтаксис Описание синтаксиса 46 Оператор вычисляет выражение, и, { $cond: [ , , ] } либо три выражения в (expression) упорядоченном списке, либо три могут именованных параметра включать Возвращает результат первого { $ifNull: выражения или результат второго [ , выражения, если первое ] } $ifNull результат. Нулевой результат включает в себя примеры неопределенных значений или Выражения имена полей и системные переменные, объекты, массивы и операторы выражений недостающие поля. Результатом второго выражения может быть null Например, чтобы по услуге «Электроснабжение», вывести значения начисления равное 300, а по другим услугам вывести неизмененные значения начислений, нужно выполнить запрос: db.SERVICES.aggregate( [ { $project: { ServiceNM: 1, NACHISLSUMMA: { 47 $cond: { if: { $eq: [ "$ServiceNM", "Электроснабжение" ] }, then: 300, else: "$NACHISLSUMMA.NachislSum" } } } } ] ) В результате запроса для услуги «Элекстроснабжение» выведено значение 300, а для других услуг массив существующих значений начислений (рис. 4.113). Рис. 4.113. Фрагмент результата выполнения запроса с оператором $cond 48 Операторы преобразования типов Операторы преобразования типов предназначены для изменения типов данных на стороне СУРаБД (таблица 19). Таблица 19 Операторы преобразования типов Выражение Описание $convert Преобразует значение в указанный $toBool тип Преобразует значение в логическое $toDate $toDecimal значение Преобразует значение в дату Преобразует значение в дробное чис- $toDouble ло Преобразует значение в число двой- $toInt $toLong ной точности Преобразует значение в целое число Преобразует значение в число с пла- $toObjectId вающей запятой Преобразует значение в ObjectId $toString (идентификатор) Преобразует значение в строку Например, чтобы преобразовать при выводе результатов запроса тип поля номера лицевых счетов абонентов из строкового формата в числовой, нужно выполнить запрос: db.ABONENT.aggregate([{$project:{_id:1, Fio:1, _id:{ $convert: { input: "$_id", to: "int"}}}}]) или db.ABONENT.aggregate([{$project:{_id:1, Fio:1, _id:{ $toInt: "$_id"}}}]) В результате запроса выведены числовые значения идентификаторов: (для примера приведены первые три абонента) /* 1 */ { 49 "Fio" : {"fam" : "Аксенов","name" : "Сергей","otc" : "Анатольевич"}, "_id" : 5488 } /* 2 */ { "Fio" : {"fam" : "Мищенко","name" : "Евгений","otc" : "Владимирович" }, "_id" : 115705 } /* 3 */ { "Fio" : "Конюхов В.С.", "_id" : 15527 } Поскольку тип из строкового преобразован в числовой, то все нули в начале строки удалены. 50