How to filter a json object within a postgresql table on a property of element?

Tibor 0
Viewed 30

I have a table "test" with a json field "description. I want to filter the table and get only the json elements from the "description" thats status = inUse.

Is there a smarter way to split the json elemnts, filter and after thate group it?

select id, json_agg("descriptionElements")  from (
select id, json_array_elements(description)::json "descriptionElements" from test
) foo 
where "descriptionElements" ->> 'state' = 'inUse'
group by id
order by id asc;

drop table if exists test;

create table test 
(
"id" int
, "description" json
);

insert into test ("id", "description") values (1, '[{"name":"Test1","operatorId":"Operator1","timestamp":"2022-09-10T15:54:58+02:00","state":"available","maxPower":{"unit":"watt","value":50.0},"color":"red"},{"name":"Test2","operatorId":"Operator2","timestamp":"2022-09-10T15:54:58+02:00","state":"unknown","maxPower":{"unit":"watt","value":20.0},"color":"yellow"},{"name":"Test3","operatorId":"Operator3","timestamp":"2022-09-10T15:54:58+02:00","state":"inUse","maxPower":{"unit":"watt","value":5.0},"color":"green"}]');

insert into test ("id", "description") values (2, '[{"name":"Test1","operatorId":"Operator1","timestamp":"2022-09-10T15:54:58+02:00","state":"available","maxPower":{"unit":"watt","value":50.0},"color":"red"},{"name":"Test2","operatorId":"Operator2","timestamp":"2022-09-10T15:54:58+02:00","state":"inUse","maxPower":{"unit":"watt","value":20.0},"color":"yellow"},{"name":"Test3","operatorId":"Operator3","timestamp":"2022-09-10T15:54:58+02:00","state":"inUse","maxPower":{"unit":"watt","value":5.0},"color":"green"}]');

insert into test ("id", "description") values (3, '[{"name":"Test1","operatorId":"Operator1","timestamp":"2022-09-10T15:54:58+02:00","state":"available","maxPower":{"unit":"watt","value":50.0},"color":"red"},{"name":"Test2","operatorId":"Operator2","timestamp":"2022-09-10T15:54:58+02:00","state":"unknown","maxPower":{"unit":"watt","value":20.0},"color":"yellow"},{"name":"Test3","operatorId":"Operator3","timestamp":"2022-09-10T15:54:58+02:00","state":"unknown","maxPower":{"unit":"watt","value":5.0},"color":"green"}]');
where-clause filter postgresql json
1 Answers

You can use jsonpath for this, though it's not exactly straightforward. You need to both filter the rows and then extract the values you're looking for:

Schema (PostgreSQL v14)

CREATE TABLE test (
    "id"          INT,
    "description" JSON
);

INSERT INTO test ("id", "description")
VALUES ( 1
       , '[
    {
      "name": "Test1",
      "operatorId": "Operator1",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "available",
      "maxPower": {
        "unit": "watt",
        "value": 50.0
      },
      "color": "red"
    },
    {
      "name": "Test2",
      "operatorId": "Operator2",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "unknown",
      "maxPower": {
        "unit": "watt",
        "value": 20.0
      },
      "color": "yellow"
    },
    {
      "name": "Test3",
      "operatorId": "Operator3",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "inUse",
      "maxPower": {
        "unit": "watt",
        "value": 5.0
      },
      "color": "green"
    }
  ]');

INSERT INTO test ("id", "description")
VALUES ( 2
       , '[
    {
      "name": "Test1",
      "operatorId": "Operator1",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "available",
      "maxPower": {
        "unit": "watt",
        "value": 50.0
      },
      "color": "red"
    },
    {
      "name": "Test2",
      "operatorId": "Operator2",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "inUse",
      "maxPower": {
        "unit": "watt",
        "value": 20.0
      },
      "color": "yellow"
    },
    {
      "name": "Test3",
      "operatorId": "Operator3",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "inUse",
      "maxPower": {
        "unit": "watt",
        "value": 5.0
      },
      "color": "green"
    }
  ]');

INSERT INTO test ("id", "description")
VALUES ( 3
       , '[
    {
      "name": "Test1",
      "operatorId": "Operator1",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "available",
      "maxPower": {
        "unit": "watt",
        "value": 50.0
      },
      "color": "red"
    },
    {
      "name": "Test2",
      "operatorId": "Operator2",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "unknown",
      "maxPower": {
        "unit": "watt",
        "value": 20.0
      },
      "color": "yellow"
    },
    {
      "name": "Test3",
      "operatorId": "Operator3",
      "timestamp": "2022-09-10T15:54:58+02:00",
      "state": "unknown",
      "maxPower": {
        "unit": "watt",
        "value": 5.0
      },
      "color": "green"
    }
  ]');

Query #1

SELECT id, 
       jsonb_path_query_array(
           description::jsonb,
           '$ ? (@[*].state == "inUse")'::jsonpath
       )
FROM test
WHERE jsonb_path_exists(
          description::jsonb,
          '$[*].state ? (@ == "inUse")'::jsonpath
      )
;
id jsonb_path_query_array
1 [{"name":"Test3","color":"green","state":"inUse","maxPower":{"unit":"watt","value":5},"timestamp":"2022-09-10T15:54:58+02:00","operatorId":"Operator3"}]
2 [{"name":"Test2","color":"yellow","state":"inUse","maxPower":{"unit":"watt","value":20},"timestamp":"2022-09-10T15:54:58+02:00","operatorId":"Operator2"},{"name":"Test3","color":"green","state":"inUse","maxPower":{"unit":"watt","value":5},"timestamp":"2022-09-10T15:54:58+02:00","operatorId":"Operator3"}]

View on DB Fiddle

userBw7b0
Related