db.test1.update(
{
"filter_field": 1,
},
[
{$set: {
"target_field": "$source_field"
}}
]
);
2021-11-12
MongoDB - Project a value based on the existance of a field
{$cond: {
if: {$eq: [ {$ifNull: ["$FIELD_NAME", null]}, null ]},
then: NumberInt(0),
else: NumberInt(1)
}},
2021-10-02
MongoDB Realm - GraphQL custom resolver specification for Mongo Atlas Search index highlight results
"highlights_field_name": {
"type": "array",
"items": {
"type": "object",
"title": "highlights_title",
"properties": {
"path": {
"type": "string"
},
"score": {
"type": "number"
},
"texts": {
"type": "array",
"items": {
"type": "object",
"title": "piece_texts",
"properties": {
"value": {
"type": "string"
},
"type": {
"type": "string"
}
}
}
}
}
}
}
Mongo Realm - Custom resolver payload specification for a field that returns an array of strings
"field_name": {
"type": "array",
"items": {
"type": "string"
}
}
Mongo Realm - Custom resolver payload specification for a field that returns an array of two integers
This is the specification for such field:
"field_name": {
"type": "array",
"items": {
"type": "integer",
"type": "integer"
}
}
2021-09-17
MongoDB - Lookup using multiple custom matching criteria
db.MAIN_COLLECTION_NAME.aggregate([
{$match: {
"ANY_FIELD_0": "ANY_VALUE",
}},
{$lookup: {
from: "COLLECTION_NAME_TO_LOOKUP_FROM",
let: {
"FIELD_ALIAS_1": "$MAIN_COLLECTION_ANY_FIELD_1",
"FIELD_ALIAS_2": "$MAIN_COLLECTION_ANY_FIELD_2",
},
pipeline: [
{$match: {
$expr: {$eq: ["$ANY_FIELD_1", "$$FIELD_ALIAS_1"]},
$expr: {$eq: ["$ANY_FIELD_2", "$$FIELD_ALIAS_2"]},
}},
],
as: "LOOKUP_RESULTS_FIELD_ALIAS"
}},
]);
2021-08-30
MySQL - CSV field to rows
select
t.*,
j.`single_value`
from
database_name.`table_name` as t
inner join
json_table(
json_array(t.`csv_source_field_name`)
'$[*]'
columns (
`single_value` varchar(50)
path '$'
)
) as j
;
Snowflake - CSV field to rows
select
table_alias.*
-- See how the field was split into several columns
-- (index, value, etc)
, flattened_alias.*
-- How to extract only the value
, TRIM(flattened_alias.value::string) as "flattened_value"
from
"database_name"."schema_name"."table_name" as table_alias
cross join
lateral flatten(
input => split(
table_alias."csv_source_field_name",
';' -- Separator
)
) as flattened_alias
;
2021-08-29
MacOS - How to rename all files in a directory, via terminal, appending a given string
for file in *.FILE_EXTENSION_IF_ANY
do
mv "$file" "STRING-TO-APPEND-${file}"
done
2021-06-10
MongoDB - Update a doubly nested array of objects
db.collection_name.update(
{
"field_to_filter_by": "abc"
},
{$set: {
"parent
.objectsArray1
.$[elemOfObjectsArray1]
.objectsArray1
.$[elemOfObjectsArray2]
.field_to_change": "the new value"
}},
{
multi: true,
upsert: false,
arrayFilters: [
{"elemOfObjectsArray1.filter_field_1": "def"},
{"elemOfObjectsArray2.filter_field_2": "ghi"},
]
}
);
2021-05-02
JavaScript - Underscore sort array of objects based on multiple fields
_(your_array).sortBy(
function(elem) {
return [elem.field_to_sort_by_1, elem.field_to_sort_by_1];
}
);
2021-04-29
JavaScript - String to file
const string_here = "asdf";
const file = new File(
[string_here],
"file_name.xml",
{type: "application/xml"}
);
PS. Then this can be treated as an input file to, for example, upload to an S3 bucket.
2021-03-01
MongoDB - Distribution of number of documents per field count
db.users.aggregate([
{$project:{
"field_count": {$size: {$objectToArray:"$$ROOT"}}
}},
{$group:{
"_id": {field_count: "$field_count"},
"document_count": {$sum: NumberInt(1)},
}},
{$project:{
"field_count": "$_id.field_count"
, "document_count": 1
, "_id": 0
}},
{$sort: {
"field_count": -1
}}
]);
PS. In other words, how many documents exist for each field count (e.g. "78 documents have 5 fields").
MongoDB - Collection fields frequency
db.COLLECTION_NAME.aggregate([
{$project:{
"arr": {$objectToArray:"$$ROOT"},
}},
{$unwind: "$arr"},
{$project: {
"_id": 0,
"k": "$arr.k",
}},
{$group: {
"_id": { "k": "$k" },
"f": {$sum: NumberInt(1)},
}},
{$project: {
"_id": 0,
"k": "$_id.k",
"f": 1,
}},
{$sort: {
"f": -1,
"k": 1,
}},
]);
MongoDB - Unwind and show document even if the array is empty
db.COLLECTION_NAME.aggregate([
{$unwind: {
"path": "$FIELD_NAME",
"preserveNullAndEmptyArrays": true
}}
]);
Subscribe to:
Posts (Atom)