Aggregate rows according to JSON array content

I have a PSQL table with json tags, that are always strings stored in a json array :

id     |  tags (json)
--------------------------
1      | ["tag1", "tag2"]
2      | ["tag12", "tag2"]
122    | []

I would like to query, for instance, the count of entries in the table containing each tag.

For instance, I’d like to get :

tag         |  count
--------------------------
tag1        | 1
tag2        | 2
tag12       | 1

I tried

SELECT tags::text AS tag, COUNT(id) AS cnt FROM my_table GROUP BY tag;

but if does not work, since it gives

tag                |  cnt
--------------------------
["tag1", "tag2"]   | 1
["tag12", "tag2"]  | 1

I guess I need to get the list of all tags in an inner query, and then for each tag count the rows that contain this tag, but I can’t find how to do that. Can you help me with that ?

Answer

Use json[b]_array_elements_text() and a lateral join to unnest the array:

select x.tag, count(*) cnt
from mytable t
cross join lateral json_array_elements_text(t.tags) as x(tag)
group by x.tag

Advertisement

Answer