Count not null json key->values in postgres table

Question

I have a postgres table with a field requirements (json field). I need to count the not null keys in the json. What would be the best approach? Currently I am querying it like this

select COALESCE(t_count,0) + COALESCE(p_count,0) as total_count from (select
CASE
  WHEN requirements->'FRIDGE_SHELF_SPACE'->'order_frequency' is not null then 1
  ELSE 0
 END as t_count,
CASE
  WHEN requirements->'SUPPLY_CHAIN'->'supply_chain_need' is not null then 1
  ELSE 0
END as p_count
from business_requirements where user_id=3561) as t

Fyi the entire json (in requirements field) looks something like this

{
  "FRIDGE_SHELF_SPACE": {
    "order_frequency": {
      "question": "How frequently will you be reordering stock?",
      "answer": "Weekly reordering"
    },
    "units_per_order": {
      "question": "What is your estimated number of units per order?",
      "answer": "10 - 20"
    }
  },
  "Rational_TAP_LINES": {

  },
  "PERMANENT_TAP_LINES": {

  },
  "SUPPLY_CHAIN": {
    "supply_chain_need": {
      "question": "What is your supply chain need?",
      "answer": "Brewing ingredients"
    },
    "supply_chain_requirements": {
      "question": "Select any of the following requirements (leave blank if not applicable)",
      "answer": [
        "Malt",
        "Yeast"
      ]
    }
  },
  "RESEARCH": {

  }
}

Jaspinder Singh · Accepted Answer · 2020-03-09 10:08:42Z

1

You can use postgres json_each function to count the keys:


select count(key) from json_each('{
  "FRIDGE_SHELF_SPACE": {
    "order_frequency": {
      "question": "How frequently will you be reordering stock?",
      "answer": "Weekly reordering"
    },
    "units_per_order": {
      "question": "What is your estimated number of units per order?",
      "answer": "10 - 20"
    }
  },
  "Rational_TAP_LINES": {

  },
  "PERMANENT_TAP_LINES": {

  },
  "SUPPLY_CHAIN": {
    "supply_chain_need": {
      "question": "What is your supply chain need?",
      "answer": "Brewing ingredients"
    },
    "supply_chain_requirements": {
      "question": "Select any of the following requirements (leave blank if not applicable)",
      "answer": [
        "Malt",
        "Yeast"
      ]
    }
  },
  "RESEARCH": {

  }
}') where exists (select 1 from json_each(value) s);

Hope this will help you :) Good luck

answered Mar 9, 2020 at 10:08

Jaspinder Singh

221 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

Inderpreet Singh Over a year ago

Thanks Jaspinder. It really helped.

Collectives™ on Stack Overflow

Count not null json key->values in postgres table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related