joining and filtering on json textfield in postgres db-CodePudding

Im working with a postgres v.10 Database

So in my example I need to return all buildings that are empty but where the electricity is active.

I have a table with all the buildungs where I can filter out the empty ones.

My problem is that info about elektricity is in a cost table and the info that i need is stored in a text field with json format. It can look like this:

{"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 Format:

{
  "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)

So if let's say I want to ask if buildung 100 had the elektricity active on 2020-02-01 the answer should be no. But after 2020-06-01 it will be Yes.

Hope you guys unserstand what I'm looking for? I want to somehow join the infos from the json with my all my ID's for the empty buildungs and also filter with a date.

CodePudding user response：

Assuming that the building id is of type integer, then the following query returns all empty buildings with active = true or false for a given ref_date :

From 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

Before 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

see the test result in dbfiddle