Search and select from Postgres NOSQL / json data

Question

Can anyone help me to search inside json datatype of Postgres database.

I have one table say transactions and contains following like values array:

column: "data" datatype: json

Single demo transaction:

{
    "id": "tran_54645bcb98ba7acfe204",
    "amount": 4200,
    ...
    "fees": [
        {
            "type": "application",
            "application": "app_1d70acbf80c8c35ce83680715c06be0d15c06be0d",
            "payment": "pay_917018675b21ca03c4fb",
            "amount": 420,
            "currency": "EUR",
            "billed_at": null
        }
    ]
}

Nested transaction list:

[
    {
        "id": "tran_54645bcb98ba7acfe204",
        "amount": 4200,
        "fees": [
            {
                "type": "application",
                "application": "app_1d70acbf80c8c35ce83680715c06be0d15c06be0d",
                "payment": "pay_917018675b21ca03c4fb",
                "amount": 420,
                "currency": "EUR",
                "billed_at": null
            }
        ]
    },
    {
        "id": "tran_98645bcb98ba7acfe204",
        "amount": 4200,
        "fees": [
            {
                "type": "application",
                "application": "app_1d70acbf80c8c35ce83680715c06be0d15c06be0d",
                "payment": "pay_917018675b21ca03c4fb",
                "amount": 120,
                "currency": "AUD",
                "billed_at": null
            }
        ]
    }
]

If I want to get all the transactions having amount > 300 AND also with currency = EUR how I can get them? I am new to Postgres so need help in understanding query building for Postgres nosql specially with PHP

Postgres version? Table definition? Also, there is no such thing as "postgre". [The short name of PostgreSQL is Postgres.](https://wiki.postgresql.org/wiki/Identity_Guidelines) Please fix. — Erwin Brandstetter, Mar 14 '15 at 18:29
I have version 9.4 You can consider table definition as follow: `table name: transaction columns: data` — mukund, Mar 15 '15 at 03:17
So you are using `json`, not `jsonb`? The latter [might be a good idea](http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype/10560761#10560761). — Erwin Brandstetter, Mar 15 '15 at 03:25
In your example, there is a single item in the nested JSON array. Is that always the case? — Erwin Brandstetter, Mar 15 '15 at 03:29
No, it wont be always it will be a large data and multiple array. it is a just a single demo transaction i have shown — mukund, Mar 15 '15 at 04:23

score 4 · Accepted Answer · edited May 23 '17 at 12:26

For just a single element in the nested JSON array:

SELECT *
FROM   transactions
WHERE (data #>> '{fees,0,amount}')::numeric > '300'
AND   (data #>> '{fees,0,currency}') = 'EUR';

For arrays with more elements you need to do more.
The manual about JSON Functions and Operators.

Since you are using Postgres 9.4: With jsonb there would be superior options, in particular for multiple array elements:

To get the sum you are asking for in the comment:

SELECT sum((data #>> '{fees,0,amount}')::numeric) AS sum_amount
FROM   ...

One more question can i get aggregate SUM(amount) for all amount > 300 as per above query — mukund, Mar 15 '15 at 04:47

Search and select from Postgres NOSQL / json data

1 Answers1