2

I have this Postgres 9.6 table def:

CREATE TABLE research (
   id int,
   data jsonb
);

and have the following sample data in it

 id |data
 ---|--------------------------------------------------------
 1  |{"name": "Sim Ltd", "sector": "business", "personnel": {"headcount": {"total": 100, "male": 50, "female": 50}}}
 2  |{"name": "EcoSmart", "sector": "business", "personnel": {"headcount": {"total": 500, "male": 460, "female": 40}}} 
 3  |{"name": "HIDN", "sector": "government", "personnel": {"headcount": {"total": 431, "male": 121, "female": 310}}} 
 4  |{"name": "RevDev", "sector": "government", "personnel": {"headcount": {"total": 15, "male": 10, "female": 5}}} 
 5  |{"name": "NEFPAN", "sector": "non-profit", "personnel": {"headcount": {"total": 5, "male": 4, "female": 1}}}

I would like to get the COUNT of DISTINCT sectors and the SUM of total, male and female by DISTINCT sectors.I want the out like this:

Sector      |Count  |Total  |Male   |Female
------------|-------|-------|-------|--------
business    |2      |600    |510    |90
government  |2      |446    |131    |315
non-profit  |1      |5      |4      |1

Would greatly appreciate if someone can guide me with writing the SQL for it. I am use Postgres 9.6. Thanks

1 Answer 1

11

You can just use:

SELECT
    data->>'sector' AS "Sector", 
    count(data->>'sector') AS "Count", 
    sum((data->'personnel'->'headcount'->>'total')::integer)  AS "Total", 
    sum((data->'personnel'->'headcount'->>'male')::integer)   AS "Male", 
    sum((data->'personnel'->'headcount'->>'female')::integer) AS "Female"
FROM
    research
GROUP BY
    data->>'sector'
ORDER BY
    data->>'sector' ;

You use two PostgreSQL JSONB operators:

jsonb -> field  => gets the field out of the json(b), returning a json(b) object
jsonb ->> field => gets the field out of the json(b), returning it as text

And you will just get:

Sector     | Count | Total | Male | Female
:--------- | ----: | ----: | ---: | -----: 
business   |     2 |   600 |  510 |     90
government |     2 |   446 |  131 |    315
non-profit |     1 |     5 |    4 |      1

However, for this kind of scenario, where your data is perfectly structured, if would make far more sense to use normalized SQL. This would be the structure of your table (without redundant data):

 CREATE TABLE normalized_research
 ( 
     id integer PRIMARY KEY,
     name text,
     sector text,
     male_headcount integer,
     female_headcount integer
 ) ;

This is how you would fill it in:

 INSERT INTO 
     normalized_research 
     (id, name, sector, male_headcount, female_headcount)
 SELECT
     id, 
     data->>'name',
     data->>'sector',
     (data->'personnel'->'headcount'->>'male')::integer,
     (data->'personnel'->'headcount'->>'female')::integer 
 FROM
     research ;

And this is the (much nicer, faster, safer) query you would make:

SELECT
     sector AS "Sector", 
     count(sector) AS "Count", 
     sum(male_headcount)+sum(female_headcount) AS "Total", 
     sum(male_headcount) AS "Male", 
     sum(female_headcount) AS "Female"
 FROM
     normalized_research
 GROUP BY
     sector
 ORDER BY
     sector ;

... that would be giving you exactly the same result.

You can find all the logic at this DBFiddle


Side notes

JSON was mostly invented to transfer and exchange data (through Ajax) between web services and web consumers (normally written in JavaScript).

Quoting JSON.org:

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999.

(emphasis mine)

It is perfectly well suited for its original purpose. But it has not been designed as a way to store data in a database. That was, somehow, an afterthought, because you can very easIly do it. You can store JSON as text, and you can already use any database, even if it is not aware of JSON.

In some occasions (normally linked to very variable data structures, and data structureS not known in advance) it is a good choice. But when your data is perfectly well structured, your schema (=structure) is well known in advance and doesn't need lots of flexibility: plain old normalized SQL is a better choice: you have data-type safety, consistency, easier indexing, referential integrity, faster access, ...

1
  • Thanks @joanolo. This works perfectly. I will take your advice and go for a normalised structure. Apr 22, 2017 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.