Postgres array of json, get all the unique values of a specific key

Question

I have a table that has data like:

id, array_json
123, [{"Name": "John", "Age": "12"}, {"Name": "Jane", "Age": "18"}, {"Name": "Jake", "Age": "34"}]
124, [{"Name": "Dolly", "Age": "12"}, {"Name": "Molly", "Age": "18"}, {"Name": "Rosa", "Age": "34"}]
123, []
125, [{"Name": "Dolly", "Age": "12"}, {"Name": "Dolly", "Age": "18"}, {"Name": "Holt", "Age": "34"}]

Its basically an id mapped to an array of jsons, which is a jsonb. I want the output to be all the unique names (Key is "Name") & their count as follows:

id, Unique_Names, Count
123, ["John", "Jane", "Jake"], 3
124, ["Dolly", "Molly", "Rosa"], 3
125, [], 0
126, ["Dolly", "Holt"], 2

How do I do this in postgres?

I cannot understand, why do you save JSON data into a postgresql field (?) while there are function in control language (e.g. php) to convert JSON <-> arrray (!). Postgresql does have string function, but it is definitely not a language mainly for string manipulation. — schweik
– schweik, Commented Jan 31, 2020 at 8:35
@schweik Postgres has a jsonb type which is essentially to store jsons. — user248884
– user248884, Commented Jan 31, 2020 at 8:37
It is your decision, but not an answer to my question. In case you need to store JSON data only, you found a perfect solution, but if you want to analyse the dadta stored in JSON object, I recommend to take a standard way. Howg — schweik
– schweik, Commented Jan 31, 2020 at 9:03

score 4 · Accepted Answer · 2020-01-31 09:02:34Z

4

You need to unnest and then aggregate back:

select t2.id
       t2.names as unique_names, 
       jsonb_array_length(names) as count 
from (
  select t.id, 
         (select jsonb_agg(distinct e.val ->> 'Name' order by e.val ->> 'Name') 
          from jsonb_array_elements(t.array_json) as e(val)) as names
  from the_table t
) AS t2
order by t2.id;

Online example

edited Jan 31, 2020 at 9:02

answered Jan 31, 2020 at 8:40

user330315

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

1 Comment

richyen Over a year ago

Wrap it as a subquery to get the Count column

Collectives™ on Stack Overflow

Postgres array of json, get all the unique values of a specific key

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related