Question 387675 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL jsonb count where keys have a certain value

How can I get filter the rows where the number of keys in a jsonb column object have a specific value? I’ve got a jsonb column that contains something like

{
  "fri": "from_home",
  "mon": "from_home",
  "sat": "dont_work",
  "sun": "dont_work",
  "thu": "commute",
  "tue": "from_home",
  "wed": "commute"
}

What I want to do is get all the rows where this column has say 3 days set to "from_home". I’ve been looking through the various pg functions for JSON but I’m just getting lost as to what would be the best fit.

Tags:

3

Answers


  1. 0

    you can use jsonb_each_text function (https://www.postgresqltutorial.com/postgresql-json-functions/postgresql-jsonb_each_text/)

    select count(*), id from 
(select id, key, value from table_source, jsonb_each_text(col_json) sub1
where value='from_home' group by id having count(*)>3;
    Login or Signup to reply.
  2. 0

    Just do a sub-select with jsonb_each.

     with t as (select '{
  "fri": "from_home",
  "mon": "from_home",
  "sat": "dont_work",
  "sun": "dont_work",
  "thu": "commute",
  "tue": "from_home",
  "wed": "commute"
}'::jsonb j)
select * from t where (select count(*) from jsonb_each(j) where value='"from_home"')=3;

    You could wrap it into a function if you wanted to make it look a bit cleaner, which would also enable you to make an index on it.

    Login or Signup to reply.
  3. 0

    jsonb_path_query returns 1 row per match. Then count and filter:

    WITH tbl(js) AS (
   SELECT '{"fri": "from_home",
            "mon": "from_home",
            "sat": "dont_work",
            "sun": "dont_work",
            "thu": "commute",
            "tue": "from_home",
            "wed": "commute"}'::jsonb
   )
SELECT *
FROM   tbl t
WHERE  (SELECT count(*) FROM jsonb_path_query(t.js, '$.* ? (@ == "from_home")')) = 3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search