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

Answer 1

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:

• Query for array elements inside JSON type
• Index for finding an element in a JSON array

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

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