加入和過濾postgresdb中的json文本欄位

我正在使用 postgres v.10 資料庫

所以在我的例子中，我需要回傳所有空的但有電的建筑物。

我有一張桌子，里面有所有的建筑，我可以在那里過濾掉空的。

我的問題是有關 elektricity 的資訊在成本表中，而我需要的資訊存盤在 json 格式的文本欄位中。它看起來像這樣：

{"splitkey_hash":{"100":[{"part":"0.0","notes":"blabla","uid":"100_0","id":"100","active_from":"2020-01-01"},{"part":"1.0","notes":"roar","uid":"100_1","id":"100","active_from":"2020-06-01"},{"part":"1.0","notes":null,"uid":"100_2","id":"100","active_from":"2021-01-01"}],"200":[{"part":"0.0","notes":null,"uid":"200_0","id":"200","active_from":"2015-01-01"}],"300":[{"part":"1.0","notes":null,"uid":"300_0","id":"300","active_from":"2013-01-01"}],"400":[{"part":"1.0","notes":"abc","uid":"400_0","id":"400","active_from":"2019-01-01"},{"part":"1.0","notes":null,"uid":"400_1","id":"400","active_from":"2020-09-01"}]}}

JSON 格式：

{
  "splitkey_hash": {
    "100": [
      {
        "part": "0.0",
        "notes": "blabla",
        "uid": "100_0",
        "id": "100",
        "active_from": "2020-01-01"
      },
      {
        "part": "1.0",
        "notes": "roar",
        "uid": "100_1",
        "id": "100",
        "active_from": "2020-06-01"
      },
      {
        "part": "1.0",
        "notes": null,
        "uid": "100_2",
        "id": "100",
        "active_from": "2021-01-01"
      }
    ],
    "200": [
      {
        "part": "0.0",
        "notes": null,
        "uid": "200_0",
        "id": "200",
        "active_from": "2015-01-01"
      }
    ],
    "300": [
      {
        "part": "1.0",
        "notes": null,
        "uid": "300_0",
        "id": "300",
        "active_from": "2013-01-01"
      }
    ],
    "400": [
      {
        "part": "1.0",
        "notes": "abc",
        "uid": "400_0",
        "id": "400",
        "active_from": "2019-01-01"
      },
      {
        "part": "1.0",
        "notes": null,
        "uid": "400_1",
        "id": "400",
        "active_from": "2020-09-01"
      }
    ]
  }
}

Building_id = id (json)
Elektricity active = "part": "1.0" (json)
Not active = "part": "0.0" (json)

因此，如果假設我想問 buildung 100 是否在 2020-02-01 啟用了電力，那么答案應該是否定的。但是在 2020-06-01 之后它將是 Yes。

希望你們理解我在找什么？我想以某種方式將來自 json 的資訊與我所有的空建筑 ID 一起加入，并用日期過濾。

uj5u.com熱心網友回復：

假設建筑物id是整數型別，那么以下查詢將回傳給定ref_date 的所有帶有 active = true 或 false 的空建筑物：

從 PostgreSQL v12 開始：

SELECT DISTINCT ON ( o->'id')
       o->'id'
     , first_value(o->>'part') OVER(PARTITION BY o->'id' ORDER BY (o->>'active_from') :: date DESC) = '1.0' AS active -- = true if active, false if not active
  FROM (your_empty_building_id_list) AS l
 INNER JOIN 
     ( your_cost_table AS t
       CROSS JOIN LATERAL jsonb_path_query((t.your_json_column :: jsonb)->'splitkey_hash'
, '$.*[*]') AS o
     )
    ON l.id = (o->>'id') :: integer
WHERE (o->>'active_from') :: date <= ref_date -- ref_date to be replaced by the effective date used for the query

在 PostgreSQL v12 之前：

SELECT DISTINCT ON ( o->'id')
       o->'id'
     , first_value(o->>'part') OVER(PARTITION BY o->'id' ORDER BY (o->>'active_from') :: date DESC) = '1.0' AS active -- = true if active, false if not active
  FROM (your_empty_building_id_list) AS l
 INNER JOIN 
     ( your_cost_table AS t
       CROSS JOIN LATERAL jsonb_each((t.your_json_column :: jsonb)->'splitkey_hash') AS m(key, value)
       CROSS JOIN LATERAL jsonb_array_elements(m.value) AS o
     ) ON l.id = o->>'id'
    WHERE (o->>'active_from') :: date <= ref_date -- ref_date to be replaced by the effective date used for the query

在dbfiddle 中查看測驗結果

標籤：json PostgreSQL的 加入

上一篇：如何在查詢中將自定義if..else陳述句添加到選定的列以將資料從資料庫匯出到excel檔案

下一篇：如何使用插入和連接在一起