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

Postgresql – count distinct records from jsonb column

I have a column stored as jsonb which is an array of number. Values look like this

enter image description here

I’d like to count the total distinct values from this column. I can get the distinct values like below

SELECT DISTINCT JSONB_ARRAY_ELEMENTS_TEXT(aimjoined) AS aims FROM mytable;

I can’t figure out how to count the total number of occurences for each of the unique values.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up also finding the solution below. It seems to perform slightly faster than that by @Stefanov.sm.

    SELECT JSONB_ARRAY_ELEMENTS_TEXT(aimjoined),COUNT(*) as count 
FROM mytable 
GROUP BY DISTINCT JSONB_ARRAY_ELEMENTS_TEXT(aimjoined) 
ORDER BY count DESC

  2. 0

    First flatten the table using a lateral join and then do a trivial group by.

    SELECT aims::numeric, count(*)
FROM mytable, 
lateral jsonb_array_elements(aimjoined) AS aims
group by aims;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search