MongoDB vs PostgreSQL JSON Aggregation

mongo-vs-postgres-json-aggregation.md

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;