Skip to content

Instantly share code, notes, and snippets.

@barlog-m
Last active November 10, 2019 18:25
Show Gist options
  • Save barlog-m/8ef00825e6f9b0e2a5884c19623aa9ac to your computer and use it in GitHub Desktop.
Save barlog-m/8ef00825e6f9b0e2a5884c19623aa9ac to your computer and use it in GitHub Desktop.
MongoDB vs PostgreSQL JSON Aggregation

MongoDB

db.spendings.insert([{
    date: ISODate("2019-04-20"),
    trx: [
    {
        name: "food",
        value: 500
    },
    {
        name: "transport",
        value: 200
    }
    ]
},
{
    date: ISODate("2019-04-21"),
    trx: [
    {
        name: "food",
        value: 300
    },
    {
        name: "transport",
        value: 100
    }
    ]
}])

db.spendings.aggregate([
    { $unwind: "$trx" },
    { $group: { _id: "$date", total: { $sum: "$trx.value" } } }
])

PostgreSQL 12

CREATE TABLE spendings
(
    id          BIGSERIAL PRIMARY KEY,
    date        DATE NOT NULL,
    doc         JSONB NOT NULL
);

INSERT INTO spendings (date, doc) VALUES ('2019-04-20',
'{
    "trx": [
    {
        "name": "food",
        "value": 500
    },
    {
        "name": "transport",
        "value": 200
    }]
}');


INSERT INTO spendings (date, doc) VALUES ('2019-04-21',
'{
    "trx": [
    {
        "name": "food",
        "value": 300
    },
    {
        "name": "transport",
        "value": 100
    }]
}');

SELECT date,
SUM (value::integer) AS sum
FROM spendings, LATERAL jsonb_path_query(doc::jsonb, '$.trx[*].value') AS value
GROUP by date;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment