Postgresql - Count of elements in nested JSON blob

Question

I have a Postgres statement that returns extracts/iterates over a json blob in the value column of a table. I am able to get a count one level deep using the query below but I can't count any deeper. I was using:

select jsonb_array_length(value -> 'team') as team_count

This returns the proper count but I cant seem to leverage this to count the names under each team.

In a perfect world I would my results to return 4 lines of results like this(title and a matching count of names):

Product Owner, 2
Technical Product Manager, 2
Data Modeler, 0 
Engineer, 0

How would I go about amending this query to give me the count of names under team? I tried all sorts of stuff but nothing that got me close.

Sample Json is below.

"team":[
  {
     "title":"Product Owner",
     "names":[
         "John Smith",
         "Jane Doe"
     ]
  },
  {
     "title":"Technical Project Manager",
     "names":[
         "Fred Flintstone",
         "Barney Rubble"
     ]
  },
  {
     "title":"Data Modeler"
  },
  {
     "title":"Engineer"
  }

Not sure what you mean with "title elements under team" . Are you maybe looking for jsonb_array_length(value -> 'team')? That count's the number of elements in the "team" array (regardless if the objects inside that array have a key named title) — user330315
– user330315, Commented Mar 18, 2020 at 20:10
@a_horse_with_no_name - I edited my question to add more clarity, trying your suggestion now. — personalt
– personalt, Commented Mar 18, 2020 at 20:20
@a_horse_with_no_name - this might be it... looking at this further — personalt
– personalt, Commented Mar 18, 2020 at 20:22

Bergi · Accepted Answer · 2020-03-18 22:00:56Z

2

You seem to be looking for

SELECT
   role -> 'title' AS team_role,
   jsonb_array_length(role -> 'names') AS member_count
FROM jsonb_array_elements(value -> 'team') AS team(role)

answered Mar 18, 2020 at 22:00

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

1 Comment

personalt Over a year ago

this was it.. Since we came from a database I made the from 'FROM TABLE, jsonb_array_elements(value -> 'team') AS team(role). that seems to get it done

Collectives™ on Stack Overflow

Postgresql - Count of elements in nested JSON blob

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