json - 从 Postgres NOSQL/json 数据中搜索和选择

Question

谁能帮我在 Postgres 数据库的 json 数据类型中搜索。

我有一张表说 transactions 并包含以下类似值数组:

列:“数据”数据类型:json

单个演示交易:

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

嵌套事务列表:

[
    {
        "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
            }
        ]
    }
]

如果我想获得所有具有 amount > 300 并且还具有 currency = EUR 的交易，我该如何获得它们？我是 Postgres 的新手，所以需要帮助来理解 Postgres nosql 的查询构建，特别是使用 PHP

Answer 1

对于嵌套 JSON 数组中的单个元素:

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

对于包含更多元素的数组，您需要做更多的事情。
关于JSON Functions and Operators的说明书.

因为您使用的是 Postgres 9.4:使用 jsonb 会有更好的选择，特别是对于多个数组元素:

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

要获得您在评论中要求的金额:

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